cluster issue

[oracle@rac1 u01]$ srvctl start database -d ORA11G
PRCR-1079 : Failed to start resource ora.ora11g.db
CRS-5017: The resource action "ora.ora11g.db start" encountered the following error:
ORA-29760: instance_number parameter not specified
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/rac1/agent/ohasd/oraagent_oracle/oraagent_oracl


Solution 03
In the pfile add instance_number=1 value.
$ vi newpfile.ora
SPFILE='+DATA/DIDAR/spfileDIDAR.ora'
instance_number=1

SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup pfile='/opt/app/oracle/product/10.2.0/db_1/dbs/newpfile.ora'

SQL> alter system set instance_number=1 scope=spfile;

SQL> shutdown immediate;

SQL> startup

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


ORA-00439: feature not enabled: Real Application Clusters & ORA-01078
when convert single instance to RAC and found below Error

Before that modified '/tmp/pfile-rac' file use *.cluster_database=true to support RAC

SQL> startup pfile='/tmp/pfile-rac'

ORA-00439: feature not enabled: Real Application Clusters
ORA-01078: failure in processing system parameters

Because => An Oracle Home disabled RAC.

So, we should relink library to enable RAC on this Oracle Home

$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk rac_on

$ make -f ins_rdbms.mk ioracle

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Moving SPFILE from file system to ASM (ORACLE RAC11g)

show parameter spfile

1. Create spfile in ASM “+RACDB_DATA” disk group
SQL> connect / as sysdba
SQL> show parameter spfile

NAME TYPE VALUE
------- ------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora

SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora' from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora';
#Or simply:
SQL> create pfile from spfile
File created.

SQL> create spfile='+RACDB_DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora';
File created.

SQL> exit
2. New spfile has been created in ASM
[grid@rac1 ~]$ asmcmd

ASMCMD> ls RACDB_DATA/racdb1/spfile*
spfileracdb1.ora

ASMCMD> exit
3. Modify initracdb11.ora on rac1 and initracdb12.ora on rac2 files to point to location in ASM
[oracle@rac1 dbs]$ echo "SPFILE='+RACDB_DATA/racdb1/spfileracdb1.ora'" > /u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora
[oracle@rac1 dbs]$ ssh rac2 "echo \"SPFILE='+RACDB_DATA/racdb1/spfileracdb1.ora'\" > /u01/app/oracle/product/11.2.0/db_1/dbs/initracdb12.ora"
4. Update OCR with new SPFILE location

[oracle@rac1 dbs]$ srvctl modify database -d racdb1 -p +RACDB_DATA/racdb1/spfileracdb1.ora
5. Rename any existing spfiles in $ORACLE_HOME/dbs

[oracle@rac1 dbs]$ mv /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora_bak

[oracle@rac1 dbs]$ ssh rac2 "mv /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb12.ora /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb12.ora_bak"
6. Restart all instances to switch to new SPFILE

[oracle@rac1 dbs]$ srvctl stop database -d racdb1
[oracle@rac1 dbs]$ srvctl start database -d racdb1

[oracle@rac1 dbs]$ srvctl status database -d racdb1
Instance racdb11 is running on node rac1
Instance racdb12 is running on node rac2
7. Connect to the database and verify spfile parameter:
[oracle@racdb1 dbs]$ <strong>connect / as sysdba</strong>

SQL> SHOW parameter spfile

NAME TYPE VALUE
------ ----------- ------------------------------
spfile string +RACDB_DATA/racdb1/spfileracdb 1.ora


++====================================================================================================

Oracle HAS setup after Hostname and IP change

How to Reconfigure Oracle Restart [ID 986740.1]

BEFORE the change of hostname and IP


[oracle@rhel5 ~]$ srvctl config asm
ASM home: /oracle/app/oracle/product/11.2.0/grid
ASM listener: LISTENER
Spfile: /oracle/app/oracle/product/11.2.0/grid/dbs/spfile+ASM.ora
ASM diskgroup discovery string:
[oracle@rhel5 ~]$ srvctl config listener
Name: LISTENER
Home: /oracle/app/oracle/product/11.2.0/grid
End points: TCP:1521
[oracle@rhel5 ~]$ srvctl config database
TEST11G
[oracle@rhel5 ~]$ srvctl config database -d TEST11G
Database unique name: TEST11G
Database name: TEST11G
Oracle home: /oracle/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/TEST11G/spfileTEST11G.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: TEST11G
Disk Groups: DATA,FRA
Services:

MAKE  the change of hostname and IP

You can chenge the IP and the hostname for a server using the command gui system-config-network. Also change the /etc/hosts file for the new IP and hostname.


old IP : 192.168.254.128 ----------new IP : 192.168.254.129
old hostname : rhel5       ----------new hostname : stndby

After we reboot the server, we see that HAS features are not functioning properly. High availabilty services has to be reconfigured.


[oracle@stndby ~]$ crsctl check has
CRS-4639: Could not contact Oracle High Availability Services
[oracle@stndby ~]$ srvctl start asm
PRCR-1070 : Failed to check if resource ora.asm is registered
Cannot communicate with crsd

Reconfigure HAS
Goto $GRID_HOME/crs/install directory and run the roothas.pl script for deconfiguration. It has to be run by the root user.


[root@stndby install]# ./roothas.pl -deconfig -force
Using configuration parameter file: ./crsconfig_params
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Delete failed, or completed with errors.
CLSU-00100: Operating System function: opendir failed with error data: 2
CLSU-00101: Operating System error message: No such file or directory
CLSU-00103: error location: scrsearch1
CLSU-00104: additional error information: cant open scr home dir scls_scr_getval
CRS-4544: Unable to connect to OHAS
CRS-4000: Command Stop failed, or completed with errors.
Successfully deconfigured Oracle Restart stack

Goto $GRID_HOME/crs/install directory and run the roothas.pl script for reconfiguration. It has to be run by the root user.


[root@stndby install]# ./roothas.pl
Using configuration parameter file: ./crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'dba'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node stndby successfully pinned.
Adding Clusterware entries to inittab

stndby 2013/01/31 04:07:34 /oracle/app/oracle/product/11.2.0/grid/cdata/stndby/backup_20130131_040734.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

Now lets check the configuration. As you can see, has has been enabled but the resources are not started, ASM and database are not registered as a resource.

[oracle@stndby ~]$ . .grid_env
[oracle@stndby ~]$ crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
[oracle@stndby ~]$ crs_stat
NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.diskmon
TYPE=ora.diskmon.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.evmd
TYPE=ora.evm.type
TARGET=ONLINE
STATE=ONLINE on stndby

NAME=ora.ons
TYPE=ora.ons.type
TARGET=OFFLINE
STATE=OFFLINE

Register and start the resources

[oracle@stndby ~]$ crsctl start resource "ora.cssd"
CRS-2672: Attempting to start 'ora.cssd' on 'stndby'
CRS-2672: Attempting to start 'ora.diskmon' on 'stndby'
CRS-2676: Start of 'ora.diskmon' on 'stndby' succeeded
CRS-2676: Start of 'ora.cssd' on 'stndby' succeeded
[oracle@stndby ~]$ crs_stat
NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=ONLINE
STATE=ONLINE on stndby

NAME=ora.diskmon
TYPE=ora.diskmon.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.evmd
TYPE=ora.evm.type
TARGET=ONLINE
STATE=ONLINE on stndby

NAME=ora.ons
TYPE=ora.ons.type
TARGET=OFFLINE
STATE=OFFLINE

lets add the listener as a resource but before we do that we need to change the listening IP of the listener.

before :

[oracle@stndby ~]$ cat /oracle/app/oracle/product/11.2.0/grid/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel5)(PORT = 1521))
)
)

after

[oracle@stndby ~]$ cat /oracle/app/oracle/product/11.2.0/grid/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = stndby)(PORT = 1521))
)
)

now we can add the listener as a resource and after that we can start it

[oracle@stndby ~]$ srvctl add listener -l LISTENER
[oracle@stndby ~]$ srvctl start listener
[oracle@stndby ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): stndby
[oracle@stndby ~]$ crs_stat ora.LISTENER.lsnr
NAME=ora.LISTENER.lsnr
TYPE=ora.listener.type
TARGET=ONLINE
STATE=ONLINE on stndby

now we can add the asm we do not need to create spfile for asm. we can use the one before.


[oracle@stndby ~]$ srvctl add asm -l LISTENER -p "/oracle/app/oracle/product/11.2.0/grid/dbs/spfile+ASM.ora"

ex: srvctl add asm -l LISTENER -p "/u01/app/11.2.0/grid/dbs/init+ASM2.ora"
[oracle@stndby ~]$ srvctl start asm
[oracle@stndby ~]$ srvctl status asm
ASM is running on stndby
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Error : ORA-01031: insufficient privileges
CRS-5017: The resource action "ora.asm start" encountered the following error:
ORA-01031: insufficient privileges

[oracle@rac2 trace]$ adrci

ADRCI: Release 11.2.0.3.0 - Production on Sat Sep 15 23:11:45 2018

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

ADR base = "/u01/app/oracle"
adrci>


SQL> select total_mb,free_mb,voting_files from v$asm_diskgroup;

  TOTAL_MB    FREE_MB V
---------- ---------- -
     50964 30678 N

srvctl config asm -a | grep -i spfile

cp +DATA/V11204/PARAMETERFILE/spfile.265.847477361 /tmp/spfile.asm

cp +DATA/V11204/PARAMETERFILE/spfile.265.847477361 /tmp/spfile.asm +DATA/asm/asmparameterfile/registry.253.986762027 create pfile='/u01/spasm.ora' from spfile='+DATA/asm/asmparameterfile/registry.253.986762027';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Finally we can add or database TEST11G

[oracle@stndby ~]$ srvctl add database -d TEST11G -o /oracle/app/oracle/product/11.2.0/dbhome_1 -p +DATA/TEST11G/spfileTEST11G.ora -r PRIMARY -s open -t immediate -a "DATA,FRA"

srvctl add database -d ORA11C -o /u01/app/oracle/11.2.0/db_1 -p +DATA/asm/asmparameterfile/registry.253.986762027 -r PRIMARY -s open -t immediate -a "DATA"

[oracle@stndby ~]$ srvctl start database -d TEST11G
[oracle@stndby ~]$ srvctl status database -d TEST11G
Database is running.

After the addition of all resources

[oracle@stndby ~]$ crs_stat
NAME=ora.DATA.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
STATE=ONLINE on stndby

NAME=ora.FRA.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
STATE=ONLINE on stndby

NAME=ora.LISTENER.lsnr
TYPE=ora.listener.type
TARGET=ONLINE
STATE=ONLINE on stndby

NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE
STATE=ONLINE on stndby

NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=ONLINE
STATE=ONLINE on stndby

NAME=ora.diskmon
TYPE=ora.diskmon.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.evmd
TYPE=ora.evm.type
TARGET=ONLINE
STATE=ONLINE on stndby

NAME=ora.ons
TYPE=ora.ons.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.test11g.db
TYPE=ora.database.type
TARGET=ONLINE
STATE=ONLINE on stndby


connect system/Welcome1
@$ORACLE_HOME/sqlplus/admin/pupbld.sql

How to Fix a Broken ASM SPFILE, held within ASM

My server rebooted itself and when it came back up, none of the databases or ASM had restarted. Everything is 11.2.0.3 or 11.2.0.1 with ASM being 11.2.0.3 – so Oracle Restart should have kicked in.

srvctl config asm -a | grep -i spfile

[oracle@rac2 trace]$ adrci

ADRCI: Release 11.2.0.3.0 - Production on Sat Sep 15 23:11:45 2018

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

ADR base = "/u01/app/oracle"
adrci> 

/u01/app/oracle/diag/asm/+asm/+ASM2/trace

$srvctl start asm
PRCR-1079 : Failed to start resource ora.asm
CRS-5017: The resource action "ora.asm start" encountered the following error:
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'myserver.mydomain.net:1899'

We have a problem in the spfile that needs to be fixed. Where is it located so that it can be converted to a pfile and corrected? The usual place to check is $ORACLE_HOME/dbs.

$cd $ORACLE_HOME/dbs
$ls spfile*
spfile* not found
$srvctl config asm -a | grep -i spfile
Spfile: +DATA/asm/asmparameterfile/registry.123.123456789

The spfile name may also be listed in the alert.log as part of a startup. It is for me in this case:


$grep "^Using.*spfile" alert_+ASM.log | tail -1
Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.123.123456789



There are numerous blog postings on the internet that explain how to start ASM, or extract the spfile, when the spfile it needs to start is in ASM, but due to a missing $GRID_HOME/gpnp/myserver/profiles/peer/profile.xml file, those were not an option here. (I think the problem is that the profile.xml is used by RAC only.)

$sqlplus / as sysasm
Connected to an idle instance.

SQL> create pfile='/home/oracle/pfile.ora' from spfile='+DATA/asm/asmparameterfile/registry.123.123456789';
create pfile='/home/oracle/pfile.ora' from spfile='+DATA/asm/asmparameterfile/registry.123.123456789'
*
ERROR at line 1:
ORA-01565: error in identifying file '+DATA/asm/asmparameterfile/registry.123.123456789'
ORA-17503: ksfdopn:2 Failed to open file +DATA/asm/asmparameterfile/registry.123.123456789
ORA-01034: ORACLE not available
$cd /app/oracle/diag/asm/+asm/+ASM/trace
$view alert_+ASM.log
Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.123.123456789
System parameters with non-default values:
  large_pool_size          = 12M
  instance_type            = "asm"
  remote_login_passwordfile= "EXCLUSIVE"
  local_listener           = "myserver.mydomain.com:1899"
  asm_diskstring           = "/dev/oracleasm/disks/disk*"
  asm_diskgroups           = "FRA"
  asm_power_limit          = 1
  diagnostic_dest          = "/app/oracle"
USER (ospid: 9251): terminating the instance due to error 119
Instance terminated by USER, pid = 9251

$vi /home/oracle/initASMtemp.ora

*.spfile="+DATA/asm/asmparameterfile/registry.123.123456789"
*.LOCAL_LISTENER='myserver.mydomain.com:1899'

$sqlplus / as sysasm
Connected to an idle instance.

SQL> startup pfile='/home/oracle/initASMtemp.ora';
ASM instance started

Total System Global Area  283930624 bytes
Fixed Size                  2181896 bytes
Variable Size             256582904 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> alter system set local_listener='myserver.mydomain.com:1899' scope=spfile;
System altered.

SQL> show parameter local

NAME             TYPE        VALUE
---------------- ----------- -------------------------------
local_listener   string      myserver.mydomain.com:189

TNS-03505: Failed to resolve name

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tnsnames.ora
  TESTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = *******************)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB)
    )
  )

Listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = E:\app\usernameproduct\11.2.0\dbhome_1)
         )
)


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ********************)(PORT = 1521))
    )
  )