Archive for the ‘RAC’ Category

Change Listener ports RAC

Posted: September 11, 2015 in RAC

Had to change some listener ports from 1521 to 8001 on a RAC environment recently here are the steps taken.

1. Check the listener status

[oracle@test0101 20299013]$ srvctl status listener -l LISTENER
Listener LISTENER is enabled
Listener LISTENER is running on node(s): test0101,test0102

2. Modify the listener

[oracle@test0101 20299013]$ srvctl modify listener -l LISTENER -p 8001

3. Bounce the listener
[oracle@test0101 20299013]$ srvctl stop listener
[oracle@test0101 20299013]$ srvctl start listener

4. Change the MGMTDB listener
[oracle@test0101 20299013]$ srvctl status listener -l MGMTLSNR
Listener MGMTLSNR is enabled
Listener MGMTLSNR is running on node(s): test0101
[oracle@test0101 20299013]$ srvctl modify listener -l MGMTLSNR -p 8001

5. Restart the MGMTDB listener
Had to also restart the MGMTDB database before restarting the listener
[oracle@test0101 20299013]$ srvctl stop MGMTLSNR
PRCR-1065 : Failed to stop resource ora.MGMTLSNR
CRS-2529: Unable to act on 'ora.MGMTLSNR' because that would require stopping or relocating 'ora.mgmtdb', but the force option was not specified
[oracle@test0101 20299013]$ srvctl stop MGMTDB
[oracle@test0101 20299013]$ srvctl stop MGMTLSNR
[oracle@test0101 20299013]$ srvctl start MGMTLSNR
[oracle@test0101 20299013]$ srvctl start MGMTDB

6. Scan Listener already at 8001
[oracle@test0101 20299013]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:8001
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:8001
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:8001
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:

11gR2 RAC recovery test

Posted: December 10, 2013 in RAC, RMAN

I was working on a recovery guide for a RAC cluster I built so I thought I would share the recovery of the DATA diskgroup. The set-up is a 2 node RAC cluster running 11.2.0.3 standard edition ASM with 2 disk groups DATA and FLASH. The OCR and voting disk are in the DATA diskgroup along with the database datafiles.

The test is to illustrate the failure in the +DATA diskgroup which will result in loss of the OCR, Voting disk and database files

Pre-recovery Steps

1. OCR & Voting Disk backup

As root

root@dbrac01mgt)# . oraenv
ORACLE_SID = [+ASM1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle

root@dbrac01mgt)# ocrconfig -showbackup
PROT-24: Auto backups for the Oracle Cluster Registry are not available
PROT-25: Manual backups for the Oracle Cluster Registry are not available

root@dbrac01mgt)# ocrconfig -manualbackup

dbrac01mgt 2013/12/09 13:35:34 /u01/app/11.2.3/grid/cdata/dbracmgt-scan/backup_20131209_133534.ocr
root@dbrac01mgt)# ocrconfig -showbackup
PROT-24: Auto backups for the Oracle Cluster Registry are not available

dbrac01mgt 2013/12/09 13:35:34 /u01/app/11.2.3/grid/cdata/dbracmgt-scan/backup_20131209_133534.ocr

2. Database Backup

oracle@dbrac01mgt /export/home/oracle $ . oraenv
ORACLE_SID = [+ASM1] ? RACTEST1
The Oracle base remains unchanged with value /u01/app/oracle

oracle@dbrac01mgt /export/home/oracle $ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 9 15:35:09 2013

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

connected to target database: RACTEST (DBID=3362036407)

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/backup/df_%d_%s_%p.bak';
RMAN>backup as compressed backupset database plus archivelog;

Starting backup at 09-DEC-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 instance=RACTEST1 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=49 RECID=2 STAMP=833722208
input archived log thread=2 sequence=1 RECID=1 STAMP=833721815
input archived log thread=2 sequence=2 RECID=3 STAMP=833722209
input archived log thread=1 sequence=50 RECID=5 STAMP=833722364
input archived log thread=2 sequence=3 RECID=4 STAMP=833722362
input archived log thread=2 sequence=4 RECID=7 STAMP=833722440
input archived log thread=1 sequence=51 RECID=6 STAMP=833722437
channel ORA_DISK_1: starting piece 1 at 09-DEC-13
channel ORA_DISK_1: finished piece 1 at 09-DEC-13
piece handle=/u01/backup/df_RACTEST_4_1.bak tag=TAG20131209T133403 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 09-DEC-13

Starting backup at 09-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ractest/datafile/system.259.833718845
input datafile file number=00002 name=+DATA/ractest/datafile/sysaux.260.833718851
input datafile file number=00003 name=+DATA/ractest/datafile/undotbs1.261.833718855
input datafile file number=00004 name=+DATA/ractest/datafile/undotbs2.263.833718873
input datafile file number=00006 name=+DATA/ractest/datafile/test.268.833721985
input datafile file number=00005 name=+DATA/ractest/datafile/users.264.833718875
channel ORA_DISK_1: starting piece 1 at 09-DEC-13
channel ORA_DISK_1: finished piece 1 at 09-DEC-13
piece handle=/u01/backup/df_RACTEST_5_1.bak tag=TAG20131209T133411 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 09-DEC-13
channel ORA_DISK_1: finished piece 1 at 09-DEC-13
piece handle=/u01/backup/df_RACTEST_6_1.bak tag=TAG20131209T133411 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-DEC-13

Starting backup at 09-DEC-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=52 RECID=8 STAMP=833722559
input archived log thread=2 sequence=5 RECID=9 STAMP=833722560
channel ORA_DISK_1: starting piece 1 at 09-DEC-13
channel ORA_DISK_1: finished piece 1 at 09-DEC-13
piece handle=/u01/backup/df_RACTEST_7_1.bak tag=TAG20131209T133603 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-DEC-13

Simulate a error

Now we have the backups we need its time to corrupt the +DATA diskgroup, the DATA diskgroup has one disk /dev/oracleasm

oracle@dbrac01mgt /export/home/oracle/sql $ dd if=/dev/zero of=/dev/oracleasm/disk1 bs=1024 count=1000000
1000000+0 records in
1000000+0 records out
1024000000 bytes (1.0 GB) copied, 5.08769 s, 201 MB/s

root@dbrac01mgt)# ocrcheck
PROT-601: Failed to initialize ocrcheck
PROC-22: The OCR backend has an invalid format

Error from the clusterlogs

2013-12-09 13:55:38.099
[client(23076)]CRS-1006:The OCR location +DATA is inaccessible. Details in /u01/app/11.2.3/grid/log/dbrac01mgt/client/ocrcheck_23076.log.

Error from the alert log

ORA-00202: control file: '+DATA/ractest/controlfile/current.256.833718843'
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_lmon_19978.trc (incident=2489):
ORA-00227: corrupt block detected in control file: (block 35, # blocks 1)
ORA-00202: control file: '+DATA/ractest/controlfile/current.256.833718843'

Steps to recover the cluster

1. Stop cluster on each node (Root user).

DBRAC01MGT
root@dbrac01mgt)# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.crsd' on 'dbrac01mgt'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.FLASH.dg' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.oc4j' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.cvu' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.dbrac01mgt.vip' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.scan3.vip' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.scan3.vip' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.scan2.vip' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.scan2.vip' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.dbrac01mgt.vip' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.dbrac01mgt.vip' on 'dbrac02mgt'
CRS-2676: Start of 'ora.scan3.vip' on 'dbrac02mgt' succeeded
CRS-2676: Start of 'ora.scan2.vip' on 'dbrac02mgt' succeeded
CRS-2677: Stop of 'ora.FLASH.dg' on 'dbrac01mgt' succeeded
CRS-2676: Start of 'ora.dbrac01mgt.vip' on 'dbrac02mgt' succeeded
CRS-4549: Unexpected disconnect while executing shutdown request.
CRS-2675: Stop of 'ora.crsd' on 'dbrac01mgt' failed
CRS-2679: Attempting to clean 'ora.crsd' on 'dbrac01mgt'
CRS-2681: Clean of 'ora.crsd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.evmd' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.asm' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.evmd' on 'dbrac01mgt' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'dbrac01mgt' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'dbrac01mgt' succeeded
CRS-2677: Stop of 'ora.asm' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.cssd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.crf' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.gipcd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.gpnpd' on 'dbrac01mgt' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'dbrac01mgt' has completed
CRS-4133: Oracle High Availability Services has been stopped.

DBRAC02MGT
root@dbrac02mgt)# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'dbrac02mgt'
CRS-2673: Attempting to stop 'ora.ctssd' on 'dbrac02mgt'
CRS-2673: Attempting to stop 'ora.evmd' on 'dbrac02mgt'
CRS-2673: Attempting to stop 'ora.asm' on 'dbrac02mgt'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.mdnsd' on 'dbrac02mgt' succeeded
CRS-2677: Stop of 'ora.evmd' on 'dbrac02mgt' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'dbrac02mgt' succeeded
CRS-2677: Stop of 'ora.asm' on 'dbrac02mgt' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'dbrac02mgt' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.cssd' on 'dbrac02mgt' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.crf' on 'dbrac02mgt' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.gipcd' on 'dbrac02mgt' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.gpnpd' on 'dbrac02mgt' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'dbrac02mgt' has completed
CRS-4133: Oracle High Availability Services has been stopped.

2. Start the cluster in the excusive mode (Root user)
As root start GI in exclusive mode on one node only:

root@dbrac01mgt)# crsctl start crs -excl -nocrs
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.mdnsd' on 'dbrac01mgt'
CRS-2676: Start of 'ora.mdnsd' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'dbrac01mgt'
CRS-2676: Start of 'ora.gpnpd' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'dbrac01mgt'
CRS-2672: Attempting to start 'ora.gipcd' on 'dbrac01mgt'
CRS-2676: Start of 'ora.cssdmonitor' on 'dbrac01mgt' succeeded
CRS-2676: Start of 'ora.gipcd' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'dbrac01mgt'
CRS-2672: Attempting to start 'ora.diskmon' on 'dbrac01mgt'
CRS-2676: Start of 'ora.diskmon' on 'dbrac01mgt' succeeded
CRS-2676: Start of 'ora.cssd' on 'dbrac01mgt' succeeded
CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'dbrac01mgt'
CRS-2672: Attempting to start 'ora.ctssd' on 'dbrac01mgt'
CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'dbrac01mgt'
CRS-2676: Start of 'ora.ctssd' on 'dbrac01mgt' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'dbrac01mgt'
CRS-2676: Start of 'ora.asm' on 'dbrac01mgt' succeeded

3. Re-create DATA disk group

oracle@dbrac01mgt /export/home/oracle/sql $ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 9 14:06:53 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SYS@+ASM1 > select PATH, HEADER_STATUS, MODE_STATUS, STATE from v$asm_disk;

PATH
------------------------------------------------------------------------------------------------------------------------------------------------------
HEADER_STATU MODE_ST STATE
------------ ------- --------
/dev/oracleasm/disk1
CANDIDATE ONLINE NORMAL

/dev/oracleasm/disk3
CANDIDATE ONLINE NORMAL

/dev/oracleasm/disk4
CANDIDATE ONLINE NORMAL

/dev/oracleasm/disk2
MEMBER ONLINE NORMAL

SYS@+ASM1 > select name, type, total_mb, free_mb from v$asm_diskgroup;

NAME TYPE TOTAL_MB FREE_MB
------------------------------ ------ ---------- ----------
FLASH 0 0

SYS@+ASM1 > create diskgroup DATA external redundancy disk '/dev/oracleasm/disk1' attribute 'COMPATIBLE.ASM' = '11.2';

Diskgroup created.

4. Restore the OCR

root@dbrac01mgt)# ocrconfig -showbackup
PROT-26: Oracle Cluster Registry backup locations were retrieved from a local copy
PROT-24: Auto backups for the Oracle Cluster Registry are not available
PROT-25: Manual backups for the Oracle Cluster Registry are not available

root@dbrac01mgt)# ocrconfig -restore /u01/app/11.2.3/grid/cdata/dbracmgt-scan/backup_20131209_133534.ocr

root@dbrac01mgt)# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2936
Available space (kbytes) : 259184
ID : 1387583758
Device/File Name : +DATA
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

5. Recreate spfile for ASM

Create a temp init.ora and then recreate the spfile

oracle@dbrac01mgt /export/home/oracle/sql $ cat /tmp/init.ora
*.asm_power_limit=1
*.diagnostic_dest='/u01/app/oracle'
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='EXCLUSIVE'
*.asm_diskstring = '/dev/oracleasm/*'

SYS@+ASM1 > create spfile='+DATA' from pfile='/tmp/init.ora';

File created.

6. Recreate the voting disk

root@dbrac01mgt)# crsctl replace votedisk +DATA
CRS-4602: Failed 27 to add voting file 050fdada770b4f45bf3a2c24adc6a7bd.
Failed to replace voting disk group with +DATA.
CRS-4000: Command Replace failed, or completed with errors.

This failed due to the diskgroup setting in ASM being unset, so we need to set it

SYS@+ASM1 > alter system set asm_diskstring='/dev/oracleasm/*';

System altered.

root@dbrac01mgt)# crsctl replace votedisk +DATA
Successful addition of voting disk 1b2343bf1ff94f2abf11bcdbf39254cd.
Successfully replaced voting disk group with +DATA.
CRS-4266: Voting file(s) successfully replaced

7. Stop/start the cluster

root@dbrac01mgt)# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.crsd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.crsd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.ctssd' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.asm' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.mdnsd' on 'dbrac01mgt' succeeded
CRS-2677: Stop of 'ora.asm' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'dbrac01mgt' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.cssd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.gipcd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.gpnpd' on 'dbrac01mgt' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'dbrac01mgt' has completed
CRS-4133: Oracle High Availability Services has been stopped.

root@dbrac01mgt)# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

8. Restore the spfile

So we now need to restore the spfile from the backup took earlier but first we need to create the directory +DATA/ractest in the asm diskgroup +DATA

oracle@dbrac01mgt /export/home/oracle $ . oraenv
ORACLE_SID = [RACTEST1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle

oracle@dbrac01mgt /export/home/oracle $ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 9 14:41:47 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup DATA add directory '+DATA/ractest';

oracle@dbrac01mgt /export/home/oracle $ . oraenv
ORACLE_SID = [+ASM1] ? RACTEST1
The Oracle base remains unchanged with value /u01/app/oracle

oracle@dbrac01mgt /export/home/oracle/sql $ rman

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 9 14:36:31 2013

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

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/RACTEST/spfileRACTEST.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/RACTEST/spfileRACTEST.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +DATA/ractest/spfileractest.ora
ORA-15173: entry 'ractest' does not exist in directory '/'
ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 158662656 bytes

Fixed Size 2226456 bytes
Variable Size 104859368 bytes
Database Buffers 46137344 bytes
Redo Buffers 5439488 bytes

RMAN> restore spfile from '/u01/backup/df_RACTEST_6_1.bak';

Starting restore at 09-DEC-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/df_RACTEST_6_1.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 09-DEC-13

9. Restore the controlfile

RMAN> startup force nomount

Oracle instance started

Total System Global Area 1043886080 bytes

Fixed Size 2234960 bytes
Variable Size 339740080 bytes
Database Buffers 696254464 bytes
Redo Buffers 5656576 bytes

RMAN> restore controlfile from '/u01/backup/df_RACTEST_6_1.bak';

Starting restore at 09-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 instance=RACTEST1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/ractest/controlfile/current.257.833726749
output file name=+FLASH/ractest/controlfile/current.256.833718843
Finished restore at 09-DEC-13

10. Restore and recover the database

RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 09-DEC-13
Starting implicit crosscheck backup at 09-DEC-13
allocated channel: ORA_DISK_1
Crosschecked 3 objects
Finished implicit crosscheck backup at 09-DEC-13

Starting implicit crosscheck copy at 09-DEC-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 09-DEC-13

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +flash/RACTEST/ARCHIVELOG/2013_12_09/thread_1_seq_52.268.833722559
File Name: +flash/RACTEST/ARCHIVELOG/2013_12_09/thread_2_seq_5.269.833722561

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/ractest/datafile/system.259.833718845
channel ORA_DISK_1: restoring datafile 00002 to +DATA/ractest/datafile/sysaux.260.833718851
channel ORA_DISK_1: restoring datafile 00003 to +DATA/ractest/datafile/undotbs1.261.833718855
channel ORA_DISK_1: restoring datafile 00004 to +DATA/ractest/datafile/undotbs2.263.833718873
channel ORA_DISK_1: restoring datafile 00005 to +DATA/ractest/datafile/users.264.833718875
channel ORA_DISK_1: restoring datafile 00006 to +DATA/ractest/datafile/test.268.833721985
channel ORA_DISK_1: reading from backup piece /u01/backup/df_RACTEST_5_1.bak
channel ORA_DISK_1: piece handle=/u01/backup/df_RACTEST_5_1.bak tag=TAG20131209T133411
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 09-DEC-13

RMAN> recover database;

Starting recover at 09-DEC-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 52 is already on disk as file +FLASH/ractest/archivelog/2013_12_09/thread_1_seq_52.268.833722559
archived log for thread 1 with sequence 53 is already on disk as file +FLASH/ractest/onlinelog/group_1.257.833718845
archived log for thread 2 with sequence 5 is already on disk as file +FLASH/ractest/archivelog/2013_12_09/thread_2_seq_5.269.833722561
archived log for thread 2 with sequence 6 is already on disk as file +FLASH/ractest/onlinelog/group_4.260.833721811
archived log file name=+FLASH/ractest/archivelog/2013_12_09/thread_1_seq_52.268.833722559 thread=1 sequence=52
archived log file name=+FLASH/ractest/archivelog/2013_12_09/thread_2_seq_5.269.833722561 thread=2 sequence=5
archived log file name=+FLASH/ractest/onlinelog/group_1.257.833718845 thread=1 sequence=53
archived log file name=+FLASH/ractest/onlinelog/group_4.260.833721811 thread=2 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-DEC-13

RMAN> alter database open resetlogs;

database opened

11. Start the cluster service on the second node

root@dbrac02mgt)# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

11gR2 RAC – crsctl status resource script

Posted: November 20, 2013 in RAC

Nice script to tidy up the output of crsctl status resource

script

#!/usr/bin/ksh
#
# 11g CRS resource status query script
#
# Description:
# - Returns formatted version of crs_stat -t, in tabular
# format, with the complete rsc names and filtering keywords
# - The argument, $RSC_KEY, is optional and if passed to the script, will
# limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
# - $ORA_CRS_HOME should be set in your environment

RSC_KEY=$1
QSTAT=-u
AWK=/bin/awk # if not available use /usr/bin/awk

# Table header:echo ""
$AWK \
'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
/u01/app/11.2.0.3/grid/bin/crsctl status resource | $AWK \
'
function ltrim(s) { sub(/^[ \t]+/, "", s); return s }
function rtrim(s) { sub(/[ \t]+$/, "", s); return s }
function trim(s) { return rtrim(ltrim(s)); }

BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; split(apptarget, atarget, ","); state=2;}
$1~/STATE/ && state == 2 {appstate = $2; split(appstate, astate, ","); state=3;}
state == 3 { split(appname, a, ",");
for (i = 1; i <= length(atarget); i++) {
printf "%-45s %-10s %-18s\n", appname, trim(atarget[i]), trim(astate[i])
};
state=0;}'

Output Example

[oracle@dba01tst ~]$ ./crs_stat.ksh
HA Resource Target State
----------- ------ -----
ora.DATA.dg ONLINE ONLINE on dba01tst
ora.DATA.dg ONLINE ONLINE on dba02tst
ora.FLASH.dg ONLINE ONLINE on dba01tst
ora.FLASH.dg ONLINE ONLINE on dba02tst
ora.LISTENER.lsnr ONLINE ONLINE on dba01tst
ora.LISTENER.lsnr ONLINE ONLINE on dba02tst
ora.LISTENER_SCAN1.lsnr ONLINE ONLINE on dba02tst
ora.LISTENER_SCAN2.lsnr ONLINE ONLINE on dba01tst
ora.LISTENER_SCAN3.lsnr ONLINE ONLINE on dba01tst
ora.asm ONLINE ONLINE on dba01tst
ora.asm ONLINE ONLINE on dba02tst
ora.cvu ONLINE ONLINE on dba01tst
ora.dba01tst.vip ONLINE ONLINE on dba01tst
ora.dba02tst.vip ONLINE ONLINE on dba02tst
ora.dbatst.db ONLINE ONLINE on dba01tst
ora.dbatst.db ONLINE ONLINE on dba02tst
ora.dbatst.dbatstinst1.svc ONLINE ONLINE on dba01tst
ora.gsd OFFLINE OFFLINE
ora.gsd OFFLINE OFFLINE
ora.net1.network ONLINE ONLINE on dba01tst
ora.net1.network ONLINE ONLINE on dba02tst
ora.oc4j OFFLINE OFFLINE
ora.ons ONLINE ONLINE on dba01tst
ora.ons ONLINE ONLINE on dba02tst
ora.scan1.vip ONLINE ONLINE on dba02tst
ora.scan2.vip ONLINE ONLINE on dba01tst
ora.scan3.vip ONLINE ONLINE on dba01tst

Moving ASM diskgroups for a RAC 11gR2

Posted: October 25, 2013 in ASM, RAC

Currently I have 2 disk groups DATA and FLASH for my RAC 11GR2 cluster I need to move them to a new storage array so here are the steps taken.

1. Create New Disk Groups FLASH2 & DATA2

Use asmca to create the new disks

Create a FLASH2 diskgroup

asm1

Create a DATA2 diskgroup

asm2

New disk groups

HA Resource Target State
———– —— —–
ora.DATA.dg ONLINE ONLINE on dba01tst
ora.DATA.dg ONLINE ONLINE on dba02tst
ora.DATA2.dg ONLINE ONLINE on dba01tst
ora.DATA2.dg ONLINE ONLINE on dba02tst
ora.FLASH.dg ONLINE ONLINE on dba01tst
ora.FLASH.dg ONLINE ONLINE on dba02tst
ora.FLASH2.dg ONLINE ONLINE on dba01tst
ora.FLASH2.dg ONLINE ONLINE on dba02tst

SQL> select name,state from v$asm_diskgroup;

NAME STATE
—————————— ———–
DATA2 MOUNTED
DATA MOUNTED
FLASH2 MOUNTED
FLASH MOUNTED

2. Moving OCR to new ASM diskgroup

[root@dba01tst /]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2964
Available space (kbytes) : 259156
ID : 192353601
Device/File Name : +DATA
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

First the new ASM diskgroup must be added and this will appear as the OCRMirror. Afterwards OCR location in the old diskgroup is removed (this will make the ocrmirror to become the ocr).

[root@dba01tst /]# ocrconfig -add +DATA2
[root@dba01tst /]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2964
Available space (kbytes) : 259156
ID : 192353601
Device/File Name : +DATA
Device/File integrity check succeeded
Device/File Name : +DATA2
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@dba01tst /]# ocrconfig -delete +DATA
[root@dba01tst /]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2964
Available space (kbytes) : 259156
ID : 192353601
Device/File Name : +DATA2
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

Related metalink note : OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) [ID 428681.1]

3. Moving Vote disks to new ASM diskgroup

[oracle@dba01tst ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 4d9126509abf4f03bf592190f8d0ae7b (/dev/asmdisk1_udev_p1) [DATA]
Located 1 voting disk(s).
[oracle@dba01tst ~]$ crsctl replace votedisk +DATA2
Successful addition of voting disk 4f4c817a9c5f4fa6bfb0ec9adc61ec39.
Successful deletion of voting disk 4d9126509abf4f03bf592190f8d0ae7b.
Successfully replaced voting disk group with +DATA2.
CRS-4266: Voting file(s) successfully replaced
[oracle@dba01tst ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 4f4c817a9c5f4fa6bfb0ec9adc61ec39 (/dev/asmdisk5_udev_p1) [DATA2]
Located 1 voting disk(s).

Related metalink note : OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) [ID 428681.1]

4. Moving server side ASM SPfile to new ASM diskgroup

SQL> show parameter spfile;

NAME TYPE VALUE
———————————— ———– ——————————
spfile string +DATA/dbatst-scan/asmparameter
file/registry.253.817905393

[oracle@dba01tst ~]$ asmcmd
ASMCMD> spget
+DATA/dbatst-scan/asmparameterfile/registry.253.817905393

SQL> create pfile=’/tmp/asm_pfile.ora’ from spfile;

File created.

SQL> create spfile=’+DATA2′ from pfile=’/tmp/asm_pfile.ora’;

New SPfile location will be logged on the ASM alert log
NOTE: updated gpnp profile ASM SPFILE to +DATA2/dbatst-scan/asmparameterfile/registry.253.828801675

ASMCMD> spset +DATA2/dbatst-scan/asmparameterfile/registry.253.828801675

Related metalink note : How to move ASM spfile to a different disk group [ID 1082943.1]

5. Moving database related files to new ASM diskgroup

SQL> show parameter control

NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string +DATA/dbatst/controlfile/curre
nt.260.817912055, +FLASH/dbats
t/controlfile/current.256.8179
12055

SQL> create pfile=’/home/oracle/pfile.ora’ from spfile;

File created.

SQL> alter system set control_files=’+DATA2′,’+FLASH2′ scope=spfile sid=’*’;

System altered.

[oracle@dba01tst ~]$ srvctl stop database -d DBATST
[oracle@dba01tst ~]$ srvctl start instance -d DBATST -i DBATST1 -o nomount
[oracle@dba01tst ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Mon Oct 14 15:10:44 2013

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

connected to target database: DBATST (not mounted)

RMAN> restore controlfile from ‘+DATA/dbatst/controlfile/current.260.817912055’;

Starting restore at 14-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 instance=DBATST1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA2/dbatst/controlfile/current.256.828803469
output file name=+FLASH2/dbatst/controlfile/current.256.828803469
Finished restore at 14-OCT-13

RMAN> exit

Recovery Manager complete.
[oracle@dba01tst ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 14 15:11:35 2013

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter control

NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string +DATA2/dbatst/controlfile/curr
ent.256.828803469, +FLASH2/dba
tst/controlfile/current.256.82
8803469
control_management_pack_access string NONE
SQL>

Related metalink note : How to duplicate a controlfile when ASM is involved [ID 345180.1]
Useful metalink note : How To Move The Database To Different Diskgroup (Change Diskgroup Redundancy) [ID 438580.1]

6. Moving SPfile to new ASM diskgroup

SQL> create pfile=’/tmp/pfile_copy.txt’ FROM SPFILE;

File created.

Edit the pfile /tmp/pfile_copy.txt to reflect the correct control files.

SQL> create spfile=’+DATA2′ FROM PFILE=’/tmp/pfile_copy.txt’;

File created.

Check the file in asmcmd and create an alias

ASMCMD> ls
DATA/
DATA2/
FLASH/
FLASH2/
ASMCMD> cd DATA2/
ASMCMD> cd DBATST/
ASMCMD> ls
CONTROLFILE/
PARAMETERFILE/
ASMCMD> cd PARAMETERFILE/
ASMCMD> ls
spfile.257.828803567
ASMCMD> pwd
+DATA2/DBATST/PARAMETERFILE
ASMCMD> cd ..
ASMCMD> mkalias +DATA2/DBATST/PARAMETERFILE/spfile.257.828803567 spfiledbatst.ora
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y PARAMETERFILE/
N spfiledbatst.ora => +DATA2/DBATST/PARAMETERFILE/spfile.257.828803567

[oracle@dba01tst ~]$ srvctl config database -d DBATST
Database unique name: DBATST
Database name: DBATST
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/DBATST/spfileDBATST.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: DBATST
Database instances: DBATST1,DBATST2
Disk Groups: DATA,FLASH,DATA2,FLASH2
Mount point paths:
Services:
Type: RAC
Database is administrator managed

[oracle@dba01tst ~]$ srvctl config database -d DBATST
Database unique name: DBATST
Database name: DBATST
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/DBATST/spfileDBATST.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: DBATST
Database instances: DBATST1,DBATST2
Disk Groups: DATA,FLASH,DATA2,FLASH2
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@dba01tst ~]$ srvctl modify database -d DBATST -p +DATA2/DBATST/spfiledbatst.ora
[oracle@dba01tst ~]$ srvctl config database -d DBATST
Database unique name: DBATST
Database name: DBATST
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA2/DBATST/spfiledbatst.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: DBATST
Database instances: DBATST1,DBATST2
Disk Groups: DATA,FLASH,DATA2,FLASH2
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@dba01tst ~]$

Modify the pfiles that give reference to the SPFile in $ORACLE_HOME/dbs

[oracle@dba01tst dbs]$ cat initDBATST1.ora
SPFILE=’+DATA/DBATST/spfileDBATST.ora’

[oracle@dba02tst dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 14 15:21:25 2013

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

Connected to an idle instance.
SQL>startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size 2234960 bytes
Variable Size 788530608 bytes
Database Buffers 247463936 bytes
Redo Buffers 5656576 bytes
Database mounted.
Database opened.

7. Moving data files to new ASM diskgroup

SQL> select ‘backup as copy datafile ‘||file#||’ format ”+DATA2”;’ from v$datafile;

‘BACKUPASCOPYDATAFILE’||FILE#||’FORMAT”+DATA2”;’
——————————————————————————–
backup as copy datafile 1 format ‘+DATA2’;
backup as copy datafile 2 format ‘+DATA2’;
backup as copy datafile 3 format ‘+DATA2’;
backup as copy datafile 4 format ‘+DATA2’;
backup as copy datafile 5 format ‘+DATA2’;

RMAN> run {
2> backup as copy datafile 1 format ‘+DATA2’;
3> backup as copy datafile 2 format ‘+DATA2’;
4> backup as copy datafile 3 format ‘+DATA2’;
5> backup as copy datafile 4 format ‘+DATA2’;
6> backup as copy datafile 5 format ‘+DATA2’;
7> }

Starting backup at 14-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=82 instance=DBATST1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/dbatst/datafile/system.256.817911967
output file name=+DATA2/dbatst/datafile/system.258.828805189 tag=TAG20131014T153948 RECID=1 STAMP=828805204
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA2/dbatst/controlfile/backup.259.828805215 tag=TAG20131014T153948 RECID=2 STAMP=828805215
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
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 14-OCT-13
channel ORA_DISK_1: finished piece 1 at 14-OCT-13
piece handle=+DATA2/dbatst/backupset/2013_10_14/nnsnf0_tag20131014t153948_0.260.828805217 tag=TAG20131014T153948 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-OCT-13

Starting backup at 14-OCT-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/dbatst/datafile/sysaux.257.817911967
output file name=+DATA2/dbatst/datafile/sysaux.261.828805219 tag=TAG20131014T154018 RECID=3 STAMP=828805244
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 14-OCT-13

Starting backup at 14-OCT-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/dbatst/datafile/undotbs1.258.817911967
output file name=+DATA2/dbatst/datafile/undotbs1.262.828805255 tag=TAG20131014T154054 RECID=4 STAMP=828805263
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 14-OCT-13

Starting backup at 14-OCT-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/dbatst/datafile/users.259.817911967
output file name=+DATA2/dbatst/datafile/users.263.828805271 tag=TAG20131014T154109 RECID=5 STAMP=828805270
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-OCT-13

Starting backup at 14-OCT-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/dbatst/datafile/undotbs2.264.817912175
output file name=+DATA2/dbatst/datafile/undotbs2.264.828805271 tag=TAG20131014T154111 RECID=6 STAMP=828805276
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-OCT-13

RMAN>

[oracle@dba01tst dbs]$ srvctl stop database -d DBATST
[oracle@dba01tst dbs]$ srvctl start instance -d DBATST -i DBATST1 -o mount
[oracle@dba01tst dbs]$ sqlplus / as sysdba
SQL> select ‘switch datafile ‘||file#||’ to copy;’ from v$datafile;

‘SWITCHDATAFILE’||FILE#||’TOCOPY;’
—————————————————————–
switch datafile 1 to copy;
switch datafile 2 to copy;
switch datafile 3 to copy;
switch datafile 4 to copy;
switch datafile 5 to copy;

SQL> select ‘recover datafile ‘||file#||’;’ from v$datafile;

‘RECOVERDATAFILE’||FILE#||’;’
———————————————————-
recover datafile 1;
recover datafile 2;
recover datafile 3;
recover datafile 4;
recover datafile 5;
[oracle@dba01tst dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Mon Oct 14 15:45:00 2013

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

connected to target database: DBATST (DBID=1790472310, not open)

RMAN> switch datafile 1 to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy “+DATA2/dbatst/datafile/system.258.828805189”

RMAN> switch datafile 2 to copy;

datafile 2 switched to datafile copy “+DATA2/dbatst/datafile/sysaux.261.828805219”

RMAN> switch datafile 3 to copy;

datafile 3 switched to datafile copy “+DATA2/dbatst/datafile/undotbs1.262.828805255”

RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy “+DATA2/dbatst/datafile/users.263.828805271”

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy “+DATA2/dbatst/datafile/undotbs2.264.828805271”

RMAN> recover datafile 1;

Starting recover at 14-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=196 instance=DBATST1 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 14-OCT-13

RMAN> recover datafile 2;

Starting recover at 14-OCT-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 14-OCT-13

RMAN> recover datafile 3;

Starting recover at 14-OCT-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 14-OCT-13

RMAN> recover datafile 4;

Starting recover at 14-OCT-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 14-OCT-13

RMAN> recover datafile 5;

Starting recover at 14-OCT-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 14-OCT-13

[oracle@dba01tst dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 14 15:46:49 2013

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter database open;

Database altered.

TAKE A FULL BACKUP

RMAN> delete datafilecopy all;

The backup cleared mine down on test

Useful metalink note : How to move a datafile from ASM to the file system [ID 390416.1]

8. Moving temp files to new ASM diskgroup

[oracle@dba01tst dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 14 15:50:54 2013

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
+DATA/dbatst/tempfile/temp.263.817912069

SQL> alter tablespace temp add tempfile ‘+data2(tempfile)’;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
+DATA/dbatst/tempfile/temp.263.817912069
+DATA2/dbatst/tempfile/temp.260.828805895

SQL> alter tablespace temp drop tempfile ‘+DATA/dbatst/tempfile/temp.263.817912069’;

Tablespace altered.

9. Moving online redo log files to new ASM diskgroup

SQL> col member format a60
SQL> select group#,member from v$logfile;

GROUP# MEMBER
———- ————————————————————
2 +DATA/dbatst/onlinelog/group_2.262.817912061
2 +FLASH/dbatst/onlinelog/group_2.258.817912063
1 +DATA/dbatst/onlinelog/group_1.261.817912059
1 +FLASH/dbatst/onlinelog/group_1.257.817912061
3 +DATA/dbatst/onlinelog/group_3.265.817912283
3 +FLASH/dbatst/onlinelog/group_3.259.817912285
4 +DATA/dbatst/onlinelog/group_4.266.817912285
4 +FLASH/dbatst/onlinelog/group_4.260.817912285

8 rows selected.

SQL> alter database add logfile member ‘+DATA2′,’+FLASH2’ to group 1;

Database altered.

SQL> alter database add logfile member ‘+DATA2′,’+FLASH2’ to group 2;

Database altered.

SQL> alter database add logfile member ‘+DATA2′,’+FLASH2’ to group 3;

Database altered.

SQL> alter database add logfile member ‘+DATA2′,’+FLASH2’ to group 4;

Database altered.

Drop the extra log file members

alter database drop logfile member ‘+FLASH/dbatst/onlinelog/group_3.259.817912285′;

SQL> select group#,member from v$logfile order by 1;

GROUP# MEMBER
———- ————————————————————
1 +DATA2/dbatst/onlinelog/group_1.259.828806063
1 +FLASH2/dbatst/onlinelog/group_1.257.828806065
2 +DATA2/dbatst/onlinelog/group_2.265.828806069
2 +FLASH2/dbatst/onlinelog/group_2.258.828806069
3 +DATA2/dbatst/onlinelog/group_3.266.828806077
3 +FLASH2/dbatst/onlinelog/group_3.259.828806077
4 +DATA2/dbatst/onlinelog/group_4.267.828806081
4 +FLASH2/dbatst/onlinelog/group_4.260.828806083

8 rows selected.

SQL> show parameter recover

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string +FLASH
db_recovery_file_dest_size big integer 800M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SQL> alter system set db_recovery_file_dest=’+FLASH2′;

System altered.

SQL> show parameter recover

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string +FLASH2
db_recovery_file_dest_size big integer 800M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0

SQL> show parameter db_create_file_dest

NAME TYPE VALUE
———————————— ———– ——————————
db_create_file_dest string +DATA
SQL> alter system set db_create_file_dest=’+DATA2’;

System altered.

SQL> show parameter db_create_file_dest

NAME TYPE VALUE
———————————— ———– ——————————
db_create_file_dest string +DATA2
SQL>

SQL> show parameter db_create_online_log_dest

NAME TYPE VALUE
———————————— ———– ——————————
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string

SQL> select max(sequence#),thread# from v$archived_log group by thread#;

MAX(SEQUENCE#) THREAD#
————– ———-
444 1
420 2

SQL> select name from v$archived_log where (sequence#=200 and thread#=1) or (sequence#=151 and thread#=2);

no rows selected

SQL> select name from v$archived_log where (sequence#=444 and thread#=1) or (sequence#=420 and thread#=2);

NAME
——————————————————————————–
+FLASH2/dbatst/archivelog/2013_10_14/thread_2_seq_420.262.828807191
+FLASH2/dbatst/archivelog/2013_10_14/thread_1_seq_444.263.828807193

10 . Dropping old disk groups

[root@dba01tst ~]# crsctl stop cluster -all
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘dba01tst’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘dba01tst’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN2.lsnr’ on ‘dba01tst’
CRS-2673: Attempting to stop ‘ora.cvu’ on ‘dba01tst’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘dba01tst’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN3.lsnr’ on ‘dba01tst’
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘dba01tst’
CRS-2673: Attempting to stop ‘ora.DATA2.dg’ on ‘dba01tst’
CRS-2673: Attempting to stop ‘ora.FLASH2.dg’ on ‘dba01tst’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘dba01tst’
CRS-2677: Stop of ‘ora.cvu’ on ‘dba01tst’ succeeded
CRS-2677: Stop of ‘ora.LISTENER_SCAN2.lsnr’ on ‘dba01tst’ succeeded
CRS-2673: Attempting to stop ‘ora.scan2.vip’ on ‘dba01tst’
CRS-2677: Stop of ‘ora.LISTENER_SCAN3.lsnr’ on ‘dba01tst’ succeeded
CRS-2673: Attempting to stop ‘ora.scan3.vip’ on ‘dba01tst’
CRS-2677: Stop of ‘ora.LISTENER_SCAN1.lsnr’ on ‘dba01tst’ succeeded
CRS-2673: Attempting to stop ‘ora.scan1.vip’ on ‘dba01tst’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘dba01tst’ succeeded
CRS-2673: Attempting to stop ‘ora.dba01tst.vip’ on ‘dba01tst’
CRS-2677: Stop of ‘ora.scan2.vip’ on ‘dba01tst’ succeeded
CRS-2677: Stop of ‘ora.scan3.vip’ on ‘dba01tst’ succeeded
CRS-2677: Stop of ‘ora.scan1.vip’ on ‘dba01tst’ succeeded
CRS-2677: Stop of ‘ora.dba01tst.vip’ on ‘dba01tst’ succeeded
CRS-2677: Stop of ‘ora.FLASH2.dg’ on ‘dba01tst’ succeeded
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘dba02tst’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘dba02tst’
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘dba02tst’
CRS-2673: Attempting to stop ‘ora.DATA2.dg’ on ‘dba02tst’
CRS-2673: Attempting to stop ‘ora.FLASH2.dg’ on ‘dba02tst’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘dba02tst’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘dba02tst’ succeeded
CRS-2673: Attempting to stop ‘ora.dba02tst.vip’ on ‘dba02tst’
CRS-2677: Stop of ‘ora.dba02tst.vip’ on ‘dba02tst’ succeeded
CRS-2677: Stop of ‘ora.FLASH2.dg’ on ‘dba02tst’ succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘dba01tst’ succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘dba02tst’ succeeded
CRS-2677: Stop of ‘ora.DATA2.dg’ on ‘dba01tst’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘dba01tst’
CRS-2677: Stop of ‘ora.asm’ on ‘dba01tst’ succeeded
CRS-2677: Stop of ‘ora.DATA2.dg’ on ‘dba02tst’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘dba02tst’
CRS-2677: Stop of ‘ora.asm’ on ‘dba02tst’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘dba02tst’
CRS-2677: Stop of ‘ora.ons’ on ‘dba02tst’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘dba02tst’
CRS-2677: Stop of ‘ora.net1.network’ on ‘dba02tst’ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘dba02tst’ has completed
CRS-2673: Attempting to stop ‘ora.ons’ on ‘dba01tst’
CRS-2677: Stop of ‘ora.ons’ on ‘dba01tst’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘dba01tst’
CRS-2677: Stop of ‘ora.net1.network’ on ‘dba01tst’ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘dba01tst’ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘dba02tst’ succeeded
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘dba02tst’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘dba02tst’
CRS-2673: Attempting to stop ‘ora.asm’ on ‘dba02tst’
CRS-2677: Stop of ‘ora.crsd’ on ‘dba01tst’ succeeded
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘dba01tst’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘dba01tst’
CRS-2673: Attempting to stop ‘ora.asm’ on ‘dba01tst’
CRS-2677: Stop of ‘ora.evmd’ on ‘dba02tst’ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘dba01tst’ succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘dba02tst’ succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘dba01tst’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘dba02tst’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘dba02tst’
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘dba02tst’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘dba02tst’
CRS-2677: Stop of ‘ora.asm’ on ‘dba01tst’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘dba01tst’
CRS-2677: Stop of ‘ora.cssd’ on ‘dba02tst’ succeeded
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘dba01tst’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘dba01tst’
CRS-2677: Stop of ‘ora.cssd’ on ‘dba01tst’ succeeded
[root@dba01tst ~]# crsctl start cluster -all
CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘dba01tst’
CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘dba02tst’
CRS-2676: Start of ‘ora.cssdmonitor’ on ‘dba01tst’ succeeded
CRS-2672: Attempting to start ‘ora.cssd’ on ‘dba01tst’
CRS-2676: Start of ‘ora.cssdmonitor’ on ‘dba02tst’ succeeded
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘dba01tst’
CRS-2672: Attempting to start ‘ora.cssd’ on ‘dba02tst’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘dba02tst’
CRS-2676: Start of ‘ora.diskmon’ on ‘dba01tst’ succeeded
CRS-2676: Start of ‘ora.diskmon’ on ‘dba02tst’ succeeded
CRS-2676: Start of ‘ora.cssd’ on ‘dba01tst’ succeeded
CRS-2672: Attempting to start ‘ora.ctssd’ on ‘dba01tst’
CRS-2672: Attempting to start ‘ora.cluster_interconnect.haip’ on ‘dba01tst’
CRS-2676: Start of ‘ora.cssd’ on ‘dba02tst’ succeeded
CRS-2672: Attempting to start ‘ora.ctssd’ on ‘dba02tst’
CRS-2672: Attempting to start ‘ora.cluster_interconnect.haip’ on ‘dba02tst’
CRS-2676: Start of ‘ora.ctssd’ on ‘dba01tst’ succeeded
CRS-2676: Start of ‘ora.ctssd’ on ‘dba02tst’ succeeded
CRS-2672: Attempting to start ‘ora.evmd’ on ‘dba01tst’
CRS-2672: Attempting to start ‘ora.evmd’ on ‘dba02tst’
CRS-2676: Start of ‘ora.evmd’ on ‘dba01tst’ succeeded
CRS-2676: Start of ‘ora.evmd’ on ‘dba02tst’ succeeded
CRS-2676: Start of ‘ora.cluster_interconnect.haip’ on ‘dba01tst’ succeeded
CRS-2672: Attempting to start ‘ora.asm’ on ‘dba01tst’
CRS-2676: Start of ‘ora.cluster_interconnect.haip’ on ‘dba02tst’ succeeded
CRS-2672: Attempting to start ‘ora.asm’ on ‘dba02tst’
CRS-2676: Start of ‘ora.asm’ on ‘dba01tst’ succeeded
CRS-2672: Attempting to start ‘ora.crsd’ on ‘dba01tst’
CRS-2676: Start of ‘ora.crsd’ on ‘dba01tst’ succeeded
CRS-2676: Start of ‘ora.asm’ on ‘dba02tst’ succeeded
CRS-2672: Attempting to start ‘ora.crsd’ on ‘dba02tst’
CRS-2676: Start of ‘ora.crsd’ on ‘dba02tst’ succeeded

Dismount the disks on one node

[oracle@dba02tst trace]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 14 16:40:20 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup data dismount;

Diskgroup altered.

SQL> alter diskgroup flash dismount;

Diskgroup altered.

Drop the disks from the other node

SQL> drop diskgroup flash including contents;

Diskgroup dropped.

SQL> drop diskgroup data including contents;

Diskgroup dropped.

SQL>

Remove the disks from OCR

[oracle@dba01tst ~]$ srvctl remove diskgroup -g DATA
PRCA-1002 : Failed to remove CRS resource ora.DATA.dg for ASM Disk Group DATA
PRCR-1028 : Failed to remove resource ora.DATA.dg
PRCR-1072 : Failed to unregister resource ora.DATA.dg
CRS-0222: Resource ‘ora.DATA.dg’ has dependency error.

[oracle@dba01tst ~]$ srvctl modify database -d DBATST -a “DATA2,FLASH2”
[oracle@dba01tst ~]$ srvctl remove diskgroup -g DATA
[oracle@dba01tst ~]$ srvctl remove diskgroup -g FLASH
[oracle@dba01tst ~]$ srvctl start database -d DBATST

Oracle 11.2.0.3 RAC DB backup failing with the following error:

Error

Mon Oct 07 01:03:28 2013
Errors in file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_ora_25286.trc:
ORA-00245: control file backup failed; target is likely on a local file system

Cause

This is down to the snapshot controlfile being on a local disk and it needs to be on a shared disk for all of the cluster in our case we need to put it on our +FLASH ASM diskgroup

FIX

So to fix the issue we need to configure the SNAPSHOT CONTROLFILE to the +FLASH ASM diskgroup in RMAN, this is done as follows:

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TESTDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/dbs/snapcf_TESTDB1.f'; # default

RMAN> configure snapshot controlfile name to '+FLASH/snapcf_TESTDB1.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FLASH/snapcf_TESTDB1.f';
new RMAN configuration parameters are successfully stored

RMAN>
RMAN> show all ;

RMAN configuration parameters for database with db_unique_name TESTDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FLASH/snapcf_TESTDB1.f';

Run a backup and check the file is created in the +FLASH diskgroup using asmcmd

oracle@testdb01tst /export/home/oracle $ asmcmd
ASMCMD> ls
DATA/
FLASH/
ASMCMD> cd FLASH/
ASMCMD> ls -l
Type         Redund  Striped  Time             Sys  Name
Y    TESTDB/
Y    TESTDBTST/
N    snapcf_testdb1.f => +FLASH/TESTDB/CONTROLFILE/Backup.302.828871329

Enabling Statspack for 11gR2 RAC

With 11g standard edition you can no longer buy the tuning and diagnostic packs so no awr reports so back to the old statspack, this is the steps i used to install statspack on my 2 node 11gR2 RAC cluster.

1. Create the perfstat schema

SQL> @?/rdbms/admin/spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: xxxxxx
xxxxxx

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing  will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: SYSAUX

Using tablespace SYSAUX as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *

Pressing  will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

SQL>
SQL> --
SQL> --  Build the tables and synonyms
SQL> connect perfstat/&&perfstat_password
Connected.
SQL> @@spctab
SQL> Rem
SQL> Rem $Header: rdbms/admin/spctab.sql /st_rdbms_11.2.0/1 2010/08/13 10:06:01 kchou Exp $
SQL> Rem
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2010, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         spctab.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*PLUS command file to create tables to hold
SQL> Rem         start and end "snapshot" statistical information
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Should be run as STATSPACK user, PERFSTAT
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    kchou       08/11/10 - Bug#9800868 - Add Missing Idle Events for
SQL> Rem                           11.2.0.2for Statspack & Standby Statspack
SQL> Rem    kchou       08/11/10 - Bug#9800868 - Add missing idle events to 11.2.0.2
SQL> Rem    cgervasi    05/13/09 - add idle event: cell worker idle
SQL> Rem    cgervasi    04/02/09 - bug8395154: missing idle events
SQL> Rem    rhlee       02/22/08 -
> Rem    cdgreen     03/14/07 - 11 F2
SQL> Rem    shsong      06/14/07 - Add idle events
SQL> Rem    cdgreen     02/28/07 - 5908354
SQL> Rem    cdgreen     04/26/06 - 11 F1
SQL> Rem    cdgreen     06/26/06 - Increase column length
SQL> Rem    cdgreen     05/10/06 - 5215982
SQL> Rem    cdgreen     05/24/05 - 4246955
SQL> Rem    cdgreen     04/18/05 - 4228432
SQL> Rem    cdgreen     03/08/05 - 10gR2 misc
SQL> Rem    vbarrier    02/18/05 - 4081984
SQL> Rem    cdgreen     10/29/04 - 10gR2_sqlstats
SQL> Rem    cdgreen     07/16/04 - 10gR2
SQL> Rem    cdialeri    03/25/04 - 3516921
SQL> Rem    vbarrier    02/12/04 - 3412853
SQL> Rem    cdialeri    12/04/03 - 3290482
SQL> Rem    cdialeri    11/05/03 - 3202706
SQL> Rem    cdialeri    10/14/03 - 10g - streams - rvenkate
SQL> Rem    cdialeri    08/05/03 - 10g F3
SQL> Rem    cdialeri    02/27/03 - 10g F2: baseline, purge
SQL> Rem    vbarrier    02/25/03 - 10g RAC
SQL> Rem    cdialeri    11/15/02 - 10g F1
SQL> Rem    cdialeri    09/27/02 - sleep4
SQL> Rem    vbarrier    03/20/02 - 2143634
SQL> Rem    vbarrier    03/05/02 - Segment Statistics
SQL> Rem    cdialeri    02/07/02 - 2218573
SQL> Rem    cdialeri    01/30/02 - 2184717
SQL> Rem    cdialeri    01/11/02 - 9.2 - features 2
SQL> Rem    cdialeri    11/30/01 - 9.2 - features 1
SQL> Rem    cdialeri    04/22/01 - Undostat changes
SQL> Rem    cdialeri    03/02/01 - 9.0
SQL> Rem    cdialeri    09/12/00 - sp_1404195
SQL> Rem    cdialeri    04/07/00 - 1261813
SQL> Rem    cdialeri    03/20/00 - Support for purge
SQL> Rem    cdialeri    02/16/00 - 1191805
SQL> Rem    cdialeri    01/26/00 - 1169401
SQL> Rem    cdialeri    11/01/99 - Enhance, 1059172
SQL> Rem    cmlim       07/17/97 - Added STATS$SQLAREA to store top sql stmts
SQL> Rem    gwood       10/16/95 - Version to run as sys without using many views
SQL> Rem    cellis.uk   11/15/89 - Created
SQL> Rem
SQL>
SQL> set showmode off echo off;

If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.

Using SYSAUX tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence

Sequence created.

Synonym created.

... Creating STATS$... tables

Table created.

Synonym created.

Table created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

Commit complete.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Index created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

View created.

Synonym created.

Table created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

Commit complete.

Synonym created.

Synonym created.

NOTE:
SPCTAB complete. Please check spctab.lis for any errors.

SQL> --  Create the statistics Package
SQL> @@spcpkg
SQL> Rem
SQL> Rem $Header: spcpkg.sql 28-jan-2008.21:06:26 arogers Exp $
SQL> Rem
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2008, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         spcpkg.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*PLUS command file to create statistics package
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Must be run as the STATSPACK owner, PERFSTAT
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    arogers     01/23/08 - 6523482 - change VM_IN/OUT_BYTES id numbers
SQL> Rem    cdgreen     03/14/07 - 11 F2
SQL> Rem    shsong      06/14/07 - Fix BUFFER_GETS
SQL> Rem    cdgreen     04/05/07 - 5691086
SQL> Rem    cdgreen     03/02/07 - use _FG for v$system_event
SQL> Rem    cdgreen     03/02/07 - 5913378
SQL> Rem    cdgreen     05/16/06 - 11 F1
SQL> Rem    cdgreen     05/10/06 - 5215982
SQL> Rem    cdgreen     05/24/05 - 4246955
SQL> Rem    cdgreen     04/18/05 - 4228432
SQL> Rem    cdgreen     02/28/05 - 10gR2 misc
SQL> Rem    vbarrier    02/18/05 - 4081984
SQL> Rem    cdgreen     01/25/05 - 4143812
SQL> Rem    cdgreen     10/29/04 - 10gR2_sqlstats
SQL> Rem    cdgreen     10/25/04 - 3970898
SQL> Rem    cdgreen     07/16/04 - 10g R2
SQL> Rem    vbarrier    03/18/04 - 3517841
SQL> Rem    vbarrier    02/12/04 - 3412853
SQL> Rem    cdialeri    12/04/03 - 3290482
SQL> Rem    cdialeri    11/05/03 - 3202706
SQL> Rem    cdialeri    10/14/03 - 10g - streams - rvenkate
SQL> Rem    cdialeri    08/05/03 - 10g F3
SQL> Rem    cdialeri    07/31/03 - 2804307
SQL> Rem    vbarrier    02/25/03 - 10g RAC
SQL> Rem    cdialeri    01/28/03 - 10g F2: baseline, purge
SQL> Rem    cdialeri    11/15/02 - 10g F1
SQL> Rem    cdialeri    10/29/02 - 2648471
SQL> Rem    cdialeri    09/11/02 - 1995145
SQL> Rem    vbarrier    04/18/02 - 2271895
SQL> Rem    vbarrier    03/20/02 - 2184504
SQL> Rem    spommere    03/19/02 - 2274095
SQL> Rem    vbarrier    03/05/02 - Segment Statistics
SQL> Rem    spommere    02/14/02 - cleanup RAC stats that are no longer needed
SQL> Rem    spommere    02/08/02 - 2212357
SQL> Rem    cdialeri    02/07/02 - 2218573
SQL> Rem    cdialeri    01/30/02 - 2184717
SQL> Rem    cdialeri    01/09/02 - 9.2 - features 2
SQL> Rem    cdialeri    11/30/01 - 9.2 - features 1
SQL> Rem    hbergh      08/23/01 - 1940915: use substrb on sql_text
SQL> Rem    cdialeri    04/26/01 - 9.0
SQL> Rem    cdialeri    09/12/00 - sp_1404195
SQL> Rem    cdialeri    04/07/00 - 1261813
SQL> Rem    cdialeri    03/28/00 - sp_purge
SQL> Rem    cdialeri    02/16/00 - 1191805
SQL> Rem    cdialeri    11/01/99 - Enhance, 1059172
SQL> Rem    cgervasi    06/16/98 - Remove references to wrqs
SQL> Rem    cmlim       07/30/97 - Modified system events
SQL> Rem    gwood.uk    02/30/94 - Modified
SQL> Rem    densor.uk   03/31/93 - Modified
SQL> Rem    cellis.uk   11/15/89 - Created
SQL> Rem
SQL>
SQL> set echo off;
Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

2. Set-up the automatic job on instance 1, this will schedule the job to run on the hour. Run as the perfstat user

SQL> @/u01/app/oracle/product/11.2.0.3/rdbms/admin/spauto.sql
SQL> Rem
SQL> Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $
SQL> Rem
SQL> Rem spauto.sql
SQL> Rem
SQL> Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         spauto.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*PLUS command file to automate the collection of STATPACK
SQL> Rem         statistics.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Should be run as the STATSPACK owner, PERFSTAT.
SQL> Rem         Requires job_queue_processes init.ora parameter to be
SQL> Rem         set to a number >0 before automatic statistics gathering
SQL> Rem         will run.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cdialeri    02/16/00 - 1191805
SQL> Rem    cdialeri    12/06/99 - 1059172, 1103031
SQL> Rem    cdialeri    08/13/99 - Created
SQL> Rem
SQL>
SQL>
SQL> spool spauto.lis
SQL>
SQL> --
SQL> --  Schedule a snapshot to be run on this instance every hour, on the hour
SQL>
SQL> variable jobno number;
SQL> variable instno number;
SQL> begin
  2    select instance_number into :instno from v$instance;
  3    dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
SQL>
SQL>
SQL> prompt

SQL> prompt     Job number for automated statistics collection for this instance
Job number for automated statistics collection for this instance
SQL> prompt     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> prompt     Note that this job number is needed when modifying or removing
Note that this job number is needed when modifying or removing
SQL> prompt     the job:
the job:
SQL> print jobno

     JOBNO
----------
        53

SQL>
SQL> prompt

SQL> prompt     Job queue process
Job queue process
SQL> prompt     ~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~
SQL> prompt     Below is the current setting of the job_queue_processes init.ora
Below is the current setting of the job_queue_processes init.ora
SQL> prompt     parameter - the value for this parameter must be greater
parameter - the value for this parameter must be greater
SQL> prompt     than 0 to use automatic statistics gathering:
than 0 to use automatic statistics gathering:
SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000
SQL> prompt

SQL>
SQL> prompt

SQL> prompt     Next scheduled run
Next scheduled run
SQL> prompt     ~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~
SQL> prompt     The next scheduled run for this job is:
The next scheduled run for this job is:
SQL> select job, next_date, next_sec
  2    from user_jobs
  3   where job = :jobno;

       JOB NEXT_DATE NEXT_SEC
---------- --------- --------------------------------
        53 03-OCT-13 16:00:00

1 row selected.

Elapsed: 00:00:00.00
SQL>
SQL> spool off;

3.Set up the job to run on the second node but to run on quarter past the hour to avoid any locks on the perfstat objects, run as the perfstat user

VARIABLE jobno NUMBER;
EXEC DBMS_JOB.SUBMIT (:jobno, 'statspack.snap;' , sysdate+0.25/24 , 'sysdate+1/24', TRUE, 2);
commit;

4.Some useful statspak information

Statspack – the basic source of information is $ORACLE_HOME/rdbms/admin/spdoc.txt
spreport.sql -> Generates a Statspack Instance report
sprepins.sql -> Generates a Statspack Instance report for the database and instance specified
sprepsql.sql -> Generates a Statspack SQL report for the SQL Hash Value specified
sprsqins.sql -> Generates a Statspack SQL report for the SQL Hash Value specified, for the database and instance specified

sppurge.sql -> Purges a limited range of Snapshot Id’s for a given database instance
sptrunc.sql -> Truncates all Performance data in Statspack tables
spuexp.par -> An export parameter file supplied for exporting the whole PERFSTAT user

RAC 11Gr2 Changing Public IPs

Posted: September 30, 2013 in RAC

Public IP addresses

1. Check current network information:

oracle@test01 /export/home/oracle $ oifcfg getif
bond0  172.23.72.0  global  public
bond1  172.23.58.16  global  cluster_interconnect

2. Delete the existing interface information from OCR

oracle@test02 /export/home/oracle $ oifcfg delif -global bond0/172.23.72.0

3. Add it back with the correct information

 oracle@test02 /export/home/oracle $ oifcfg setif -global bond0/172.29.2.0:public

4. Shutdown the cluster

root@test02)$ /u01/app/11.2.0.3/grid/bin/crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'test02'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'test02'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'test02'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'test02'
CRS-2673: Attempting to stop 'ora.testdb.testdb.svc' on 'test02'
CRS-2677: Stop of 'ora.testdb.testdb.svc' on 'test02' succeeded
CRS-2673: Attempting to stop 'ora.testdb.db' on 'test02'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'test02' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'test02'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'test02' succeeded
CRS-2673: Attempting to stop 'ora.test02.vip' on 'test02'
CRS-2677: Stop of 'ora.test02.vip' on 'test02' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'test02' succeeded
CRS-2677: Stop of 'ora.testdb.db' on 'test02' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'test02'
CRS-2673: Attempting to stop 'ora.FLASH.dg' on 'test02'
CRS-2677: Stop of 'ora.FLASH.dg' on 'test02' succeeded
CRS-2673: Attempting to stop 'ora.crsd' on 'test01'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'test01'
CRS-2673: Attempting to stop 'ora.testdb.testdb.svc' on 'test01'
CRS-2673: Attempting to stop 'ora.cvu' on 'test01'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'test01'
CRS-2673: Attempting to stop 'ora.oc4j' on 'test01'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'test01'
CRS-2677: Stop of 'ora.testdb.testdb.svc' on 'test01' succeeded
CRS-2673: Attempting to stop 'ora.testdb.db' on 'test01'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'test01'
CRS-2677: Stop of 'ora.cvu' on 'test01' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'test01' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'test01'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'test01' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'test01'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'test01' succeeded
CRS-2673: Attempting to stop 'ora.test01.vip' on 'test01'
CRS-2677: Stop of 'ora.scan3.vip' on 'test01' succeeded
CRS-2677: Stop of 'ora.scan2.vip' on 'test01' succeeded
CRS-2677: Stop of 'ora.test01.vip' on 'test01' succeeded
CRS-2677: Stop of 'ora.testdb.db' on 'test01' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'test01'
CRS-2673: Attempting to stop 'ora.FLASH.dg' on 'test01'
CRS-2677: Stop of 'ora.FLASH.dg' on 'test01' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'test01' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'test02' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'test02'
CRS-2677: Stop of 'ora.asm' on 'test02' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'test02'
CRS-2677: Stop of 'ora.ons' on 'test02' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'test02'
CRS-2677: Stop of 'ora.net1.network' on 'test02' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'test02' has completed
CRS-2677: Stop of 'ora.crsd' on 'test02' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'test02'
CRS-2673: Attempting to stop 'ora.evmd' on 'test02'
RS-2673: Attempting to stop 'ora.asm' on 'test02'
CRS-2677: Stop of 'ora.DATA.dg' on 'test01' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'test01'
CRS-2677: Stop of 'ora.asm' on 'test01' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'test01'
CRS-2677: Stop of 'ora.evmd' on 'test02' succeeded
CRS-2677: Stop of 'ora.ons' on 'test01' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'test01'
CRS-2677: Stop of 'ora.net1.network' on 'test01' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'test01' has completed
CRS-2677: Stop of 'ora.crsd' on 'test01' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'test01'
CRS-2673: Attempting to stop 'ora.evmd' on 'test01'
CRS-2673: Attempting to stop 'ora.asm' on 'test01'
CRS-2677: Stop of 'ora.evmd' on 'test01' succeeded
CRS-2677: Stop of 'ora.asm' on 'test02' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'test02'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'test02' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'test01' succeeded
CRS-2677: Stop of 'ora.asm' on 'test01' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'test01'
CRS-2677: Stop of 'ora.ctssd' on 'test02' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'test02'
CRS-2677: Stop of 'ora.cssd' on 'test02' succeeded
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'test01' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'test01'
CRS-2677: Stop of 'ora.cssd' on 'test01' succeeded

5. Modify the IP address at network layer, DNS and /etc/hosts file to reflect the change. Files to modify/check are:

   - /etc/sysconfig/network-script/ifcfg-eth0
   - /etc/sysconfig/network
   - /etc/hosts

6. Restart network interface to activate changes

 $ ifdown eth0
 $ ifup eth0

6. Restart the cluster

root@test01)$ /u01/app/11.2.0.3/grid/bin/crsctl start cluster -all
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'test01'
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'test02'
CRS-2676: Start of 'ora.cssdmonitor' on 'test02' succeeded
CRS-2676: Start of 'ora.cssdmonitor' on 'test01' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'test01'
CRS-2672: Attempting to start 'ora.cssd' on 'test02'
CRS-2672: Attempting to start 'ora.diskmon' on 'test01'
CRS-2672: Attempting to start 'ora.diskmon' on 'test02'
CRS-2676: Start of 'ora.diskmon' on 'test02' succeeded
CRS-2676: Start of 'ora.diskmon' on 'test01' succeeded
CRS-2676: Start of 'ora.cssd' on 'test02' succeeded
CRS-2676: Start of 'ora.cssd' on 'test01' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'test02'
CRS-2672: Attempting to start 'ora.ctssd' on 'test01'
CRS-2676: Start of 'ora.ctssd' on 'test02' succeeded
CRS-2676: Start of 'ora.ctssd' on 'test01' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'test02'
CRS-2672: Attempting to start 'ora.evmd' on 'test01'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'test01'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'test02'
CRS-2676: Start of 'ora.evmd' on 'test01' succeeded
CRS-2676: Start of 'ora.evmd' on 'test02' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'test01' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'test02' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'test01'
CRS-2672: Attempting to start 'ora.asm' on 'test02'
CRS-2676: Start of 'ora.asm' on 'test01' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'test01'
CRS-2676: Start of 'ora.asm' on 'test02' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'test02'
CRS-2676: Start of 'ora.crsd' on 'test01' succeeded
CRS-2676: Start of 'ora.crsd' on 'test02' succeeded

 Changing VIPs node 1

1. Check current configuration

oracle@test01 /export/home/oracle $ srvctl config nodeapps -a
Network exists: 1/172.23.72.0/255.255.248.0/bond0, type static
VIP exists: /test01-vip/172.29.2.31/172.23.72.0/255.255.248.0/bond0, hosting node test01
VIP exists: /test02-vip/172.29.2.33/172.23.72.0/255.255.248.0/bond0, hosting node test02

2. Stop the database instance and VIP:

oracle@test01 /export/home/oracle $ srvctl stop instance -d testdb -n test01
oracle@test01 /export/home/oracle $ srvctl stop vip -n test01 -f

3. Ensure VIP is offline and VIP is not bounded to network interface

crsctl stat res -t
ifconfig -a

4. Alter the VIP

root@test01)$ srvctl modify nodeapps -n test01 -A 172.29.2.31/255.255.255.0/bond0

5. Verify the change

root@test01)$ srvctl config nodeapps -n test01 -a
-n <node_name> option has been deprecated.
Network exists: 1/172.29.2.0/255.255.255.0/bond0, type static
VIP exists: /test01-vip/172.29.2.31/172.29.2.0/255.255.255.0/bond0, hosting node test01

6. Start the database instance and VIP

oracle@test01 /export/home/oracle $ srvctl start vip -n test01
oracle@test01 /export/home/oracle $ srvctl start instance -d testdb -n test01

8. Ensure VIP is online and VIP is bounded to network interface

 $ crsctl stat res -t
 $ ifconfig -a

9. Repeat the steps for each node in the cluster

Changing SCAN IPs

1. Stop the SCAN listener and the SCAN

srvctl stop scan_listener
srvctl stop scan
srvctl status scan

2. Check the current IP address(es) of the SCAN

srvctl config scan

3. Refresh the SCAN with the new IP addresses from the DNS entry:

srvctl modify scan -n testdb-scan

4. Check whether the SCAN has been changed

oracle@test01 /export/home/oracle $ srvctl config scan
SCAN name: testdb-scan, Network: 1/172.29.2.0/255.255.255.0/bond0
SCAN VIP name: scan1, IP: /testdb-scan/172.29.2.36
SCAN VIP name: scan2, IP: /testdb-scan/172.29.2.35
SCAN VIP name: scan3, IP: /testdb-scan/172.29.2.34