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:

Setting up Instance Caging

Posted: July 3, 2015 in Uncategorized

A great way to limit CPU on a shared database server is by using instance caging, below are the steps to implement instance caging.

1. Check to see if instance caging is enabled

SQL> select name from v$rsrc_plan where is_top_plan = 'TRUE' and cpu_managed='ON';
no rows selected

Its not enabled

2. Set the CPU count

Set the cpu_count to what figure your require to limit the CPU in the instance to

SQL> alter system set cpu_count = 4;
System altered.

3. Enable instance caging

SQL> alter system set resource_manager_plan = 'default_plan';
System altered.

4. Check to see if instance caging is enabled

SQL> select name from v$rsrc_plan where is_top_plan = 'TRUE' and cpu_managed='ON';
NAME
--------------------------------
DEFAULT_PLAN

You have now enabled instance caging limiting your database to a maximum of 4 cpu’s to use

oratop – monitoring tool

Posted: May 22, 2014 in Tools

Just found out about the new oracle tool oratop, not used it live issues yet but it looks a great tool if your on linux and on 11.2.0.3 or >

The oracle note on the product is – oratop – Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

Sample output

oratop

Very simple to run just download the binary from oracle rename it to oratop, give execute permissions then set your session to the database you want to monitor and run
– $ ./oratop -i 10 / as sysdba
replace the 10 with how ever many secs you want the interval to be

Review oracle’s guide on the tool for all the options – oratop

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.

Got the following error when trying to add a target on 12c Grid Control

TEST:
oracle.sysman.emSDK.core.target.DuplicateEntityException: ORA-20500: TEST:oracle_database:testprd:https://testprd:3872/emd/main/:3:0 ORA-06512: at "SYSMAN.EM_TARGET", line 3347 ORA-06512: at line 1 - ORA-20500: TEST:oracle_database:testprd:https://testprd:3872/emd/main/:3:0 ORA-06512: at "SYSMAN.EM_TARGET", line 3347 ORA-06512: at line 1

To fix this we just need to remove the entry from the repository, so connect to the repository database as sysman and run the following

exec mgmt_admin.delete_target(‘TEST’,'oracle_database’);

It may take a few mins to delete but you can check by either looking in the health overview window in grid control – setup > manage cloud control > health overview and click on the value next to deleted targets this will list when the target was deleted.

You can also check by running the following sql against the repository database as sysman

set linesize 180
column TARGET_NAME format a30
column TARGET_TYPE format a15
column DISPLAY_NAME format a30
column HOST_NAME format a30
column EMD_URL format a45
select TARGET_NAME, TARGET_TYPE, DISPLAY_NAME, HOST_NAME, EMD_URL from mgmt_targets where target_type=’oracle_database’ and target_name like ‘%TEST%’;

Removing Disk from a ASM diskgroup

Posted: November 22, 2013 in ASM

Steps to remove a disk from a ASM diskgroup

So we have the following disks and the plan is to remove VOLE

SQL> select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat;

GROUP_NUMBER NAME TOTAL_MB FREE_MB
------------ ------------------------------ ---------- ----------
0 1019 0
1 VOLA 1019 842
1 VOLB 1019 841
1 VOLC 1019 841
2 VOLD 1019 792
1 VOLE 1019 841

Drop the disk from the Disk group

SQL> alter diskgroup DATADG drop disk 'VOLE';

Diskgroup altered.

Check the re-balance status

SQL> select sysdate, GROUP_NUMBER, OPERATION, STATE, POWER, ACTUAL, SOFAR, EST_WORK
2 EST_RATE, EST_MINUTES from v$asm_operation;

SYSDATE GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_RATE
--------- ------------ ----- ---- ---------- ---------- ---------- ----------
EST_MINUTES
-----------
12-SEP-08 1 REBAL RUN 1 1 1 193
3

Now the disk is not assigned to a disk group

SQL> select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat;

GROUP_NUMBER NAME TOTAL_MB FREE_MB
------------ ------------------------------ ---------- ----------
0 1019 0
1 VOLA 1019 783
1 VOLB 1019 783
1 VOLC 1019 782
2 VOLD 1019 792

Check the disk status now VOLE should be listed as FORMER and is ready to be re-used

SQL> select PATH, HEADER_STATUS, MODE_STATUS, STATE from v$asm_disk;
PATH HEADER_STATU MODE_ST STATE
-----------
ORCL:VOLE FORMER ONLINE NORMAL
ORCL:VOLF PROVISIONED ONLINE NORMAL
ORCL:VOLA MEMBER ONLINE NORMAL
ORCL:VOLB MEMBER ONLINE NORMAL
ORCL:VOLC MEMBER ONLINE NORMAL
ORCL:VOLD MEMBER ONLINE NORMAL
6 rows selected.

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