The following are the steps I used to install Oracle 12c on Red Hat 6

Download the Software from Technet
The 2 zip files needed are linuxamd64_12c_database_1of2.zip & linuxamd64_12c_database_2of2.zip

OS Settings
Ensure the Hostname is in /etc/hosts

[root@dba05tst stage]# cat /etc/hosts |grep dba05tst
172.23.11.222 dba05tst

Disable the Firewall Settings

[root@dba05tst stage]# service iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ]
[root@dba05tst stage]# service iptables stop
iptables: Flushing firewall rules: [ OK ]
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Unloading modules: [ OK ]
[root@dba05tst stage]# chkconfig iptables off
[root@dba05tst stage]# service network restart
Shutting down interface eth0: Device state: 3 (disconnected)
[ OK ]
Shutting down loopback interface: [ OK ]
Bringing up loopback interface: [ OK ]
Bringing up interface eth0: Active connection state: activated
Active connection path: /org/freedesktop/NetworkManager/ActiveConnection/1
[ OK ]

Kernel Settings
Add the following settings to /etc/sysctl.conf

#Oracle settings
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

Apply the settings using sysctl -p

[root@dba05tst stage]# sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
net.bridge.bridge-nf-call-ip6tables = 0
net.bridge.bridge-nf-call-iptables = 0
net.bridge.bridge-nf-call-arptables = 0
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

Create the Users and Groups and directories

[root@dba05tst stage]# groupadd -g 500 oinstall
[root@dba05tst stage]# groupadd -g 501 dba
[root@dba05tst stage]# useradd -u 502 -g oinstall -G dba oracle
[root@dba05tst stage]# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: it is too simplistic/systematic
Retype new password:
passwd: all authentication tokens updated successfully.
[root@dba05tst stage]# mkdir -p /u01/app/oracle/product/12.1.0
[root@dba05tst stage]# chown -R oracle:oinstall /u01
[root@dba05tst stage]# chmod -R 775 /u01

Add the following Entries to /etc/security/limits.conf

oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768

Change Settings in /etc/security/limits.d/90-nproc.conf and /etc/selinux/config

The setting should be as follows:

[root@dba05tst stage]# cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
# targeted - Targeted processes are protected,
# mls - Multi Level Security protection.
SELINUXTYPE=targeted

[root@dba05tst stage]# cat /etc/security/limits.d/90-nproc.conf
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.

# Comment the following Line
* soft nproc 1024

# Include the following Line
* - nproc 16384

AdditonalSoftware packages
Make sure the software disk is mounted

[root@dba05tst Packages]# mkdir /tmp/packages
[root@dba05tst Packages]# pwd
/media/RHEL_6.2 x86_64 Disc 1/Packages

rpm -Uvh kernel-headers-2.6.32-220.el6.x86_64.rpm
rpm -Uvh glibc-headers-2.12-1.47.el6.x86_64.rpm
rpm -Uvh compat-libcap1-1.10-1.x86_64.rpm
rpm -Uvh libmpcdec-1.2.6-6.1.el6.x86_64.rpm
rpm -Uvh mpfr-2.4.1-6.el6.x86_64.rpm
rpm -Uvh cpp-4.4.6-3.el6.x86_64.rpm
rpm -Uvh libgomp-4.4.6-3.el6.x86_64.rpm
rpm -Uvh ppl-0.10.2-11.el6.x86_64.rpm
rpm -Uvh cloog-ppl-0.15.7-1.2.el6.x86_64.rpm
rpm -Uvh gcc-4*x86_64*
rpm -Uvh binutils-2*x86_64*
rpm -Uvh glibc-2*x86_64* nss-softokn-freebl-3*x86_64*
rpm -Uvh glibc-2*i686* nss-softokn-freebl-3*i686*
rpm -Uvh compat-libstdc++-33*x86_64*
rpm -Uvh glibc-common-2*x86_64*
rpm -Uvh glibc-devel-2*x86_64*
rpm -Uvh glibc-devel-2*i686*
rpm -Uvh glibc-headers-2*x86_64*
rpm -Uvh elfutils-libelf-0*x86_64*
rpm -Uvh elfutils-libelf-devel-0*x86_64*
rpm -Uvh gcc-4*x86_64*
rpm -Uvh gcc-c++-4*x86_64*
rpm -Uvh ksh-x86_64
rpm -Uvh libaio-0*x86_64*
rpm -Uvh libaio-devel-0*x86_64*
rpm -Uvh libaio-0*i686*
rpm -Uvh libaio-devel-0*i686*
rpm -Uvh libgcc-4*x86_64*
rpm -Uvh libgcc-4*i686*
rpm -Uvh libstdc++-4*x86_64*
rpm -Uvh libstdc++-4*i686*
rpm -Uvh libstdc++-devel-4*x86_64*
rpm -Uvh make-3.81*x86_64*
rpm -Uvh numactl-devel-2*x86_64*
rpm -Uvh sysstat-9*x86_64*
rpm -Uvh compat-libstdc++-33*i686*
rpm -Uvh compat-libcap*
rpm -Uvh openssh-clients-5.3p1-70.el6.x86_64.rpm

Add the following to .profile or bash_profile

oracle@dba05tst ~]$ cat .profile
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0
export ORACLE_SID=TEST12C
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

Unzip the software

[oracle@dba05tst stage]$ unzip linuxamd64_12c_database_1of2.zip
[oracle@dba05tst stage]$ unzip linuxamd64_12c_database_2of2.zip

CVU Bug [ID 1304727.1]

Change CV_ASSUME_DISTID=OEL4 to CV_ASSUME_DISTID=OEL6 in /stage/database/stage/cvu/cv/admin/cvu_config

Start the installer

[oracle@dba05tst database]$ xclock
Error: Can't open display:
[oracle@dba05tst database]$ export DISPLAY=172.23.0.90:0.0
[oracle@dba05tst database]$ xclock
[oracle@dba05tst database]$ pwd
/stage/database
[oracle@dba05tst database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 15944 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4031 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-11-11_02-10-39PM. Please wait ...

Untick I wish to receive security updates via My Oracle Support and press Next then Yes
Select Skip software updates then next
Select Install database software only then next
Select Single instance database installation then next
Select English and English (United Kingdom) then next
Select database edition then next
Select oracle base and oracle software location then next
Select inventory location and group name then next
Select os groups then next
Make sure no issues then press install

Run the root.sh scripts

[root@dba05tst Packages]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@dba05tst Packages]# /u01/app/oracle/product/12.1.0/root.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/12.1.0

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

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

Changing Default profile limits

Posted: October 23, 2013 in Uncategorized

How to change the default profile limits useful for setting the password life time to stop the password timing out and asking the user to change the password especially when applications are connecting to the DB.

SYSTEM@WEBDB1> @qprofile
Enter value for 1: DEFAULT
old   1: select * from dba_profiles where profile =upper(‘&1’)
new   1: select * from dba_profiles where profile =upper(‘DEFAULT’)

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED
DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7

16 rows selected.

SYSTEM@WEBDB1> alter profile default limit password_life_time unlimited;

Profile altered.

SYSTEM@WEBDB1> alter profile default limit failed_login_attempts unlimited;

Profile altered.

SYSTEM@WEBDB1> alter profile default limit password_lock_time unlimited;

Profile altered.

SYSTEM@WEBDB1> alter profile default limit password_grace_time unlimited;

Profile altered.

SYSTEM@WEBDB1> @qprofile
Enter value for 1: DEFAULT
old   1: select * from dba_profiles where profile =upper(‘&1’)
new   1: select * from dba_profiles where profile =upper(‘DEFAULT’)

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED
DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

16 rows selected.

SYSTEM@WEBDB1>

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

Got the following error on a 11.2.0.3 RAC database, after investigation it turns out to be a Oracle Configuration Manager (OCM) error as we don’t use this product its safe to  just disable the jobs

Error

Sun Oct 13 06:00:01 2013
Errors in file /u01/app/oracle/diag/rdbms/test/TEST1/trace/TEST1_j004_20464.trc:
ORA-12012: error on auto execute of job “ORACLE_OCM”.”MGMT_CONFIG_JOB_2_1″
ORA-29280: invalid directory path
ORA-06512: at “ORACLE_OCM.MGMT_DB_LL_METRICS”, line 2436
ORA-06512: at line 1
Sun Oct 13 06:00:23 2013

Fix

Login to any instance in the cluster as sysdba and run

SQL> exec dbms_scheduler.disable(‘ORACLE_OCM.MGMT_CONFIG_JOB’);

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.disable(‘ORACLE_OCM.MGMT_STATS_CONFIG_JOB’)

PL/SQL procedure successfully completed.

Oracle Reference documents – 1453959.1

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