Changing the Processes Parameter

How to change the Process Parameter on standby side of a RAC database

Recently we where getting ORA-00020: No more process state objects available one of our Production system. When I looked at the Processes parameter on Primary side it was set to 1000 and on standby side it was set to 200 (default).
So we had to change the Processes Parameter to match the Production system.

1) We will disable the log shipping on the Primary side.


alter system set log_archive_dest_2='DEFER' SCOPE=MEMORY;

2)We will login into standby database and cancel the recovery process(MRP), Once the standby database is out of Recovery Mode, we can proceed with changing the Process parameter.

alter database recover managed standby database cancel;

3) Altering the Processes Parameter


alter system set processes=1000 scope=spfile;

4)Once the Parameter is changed, we would have to bounce the Database as Processes is a static parameter
 
srvctl stop database -d ****


5) Now we can start the standby database in Mount sate and enable to log shipping on Primary Side.

srvctl start database -d **** -o mount

alter system set log_archive_dest_2='ENABLE' SCOPE=MEMORY;

6) Once the log shipping has been started, we can start the recovery Process on the standby side and at this point we should be done.


   Let's check the Dataguard status for any Log Gap.


DGMGRL> show database ****_dg

Database - xxxdb11p_dg

  Enterprise Manager Name: xxxb11p_dg.xxxxxx.com
  Role:                    PHYSICAL STANDBY
  Intended State:          APPLY-ON
  Transport Lag:           0 seconds
  Apply Lag:               0 seconds
  Real Time Query:         OFF
  Instance(s):
    edwdb11p1 (apply instance)
    edwdb11p2
    edwdb11p3
    edwdb11p4
    edwdb11p5
    edwdb11p6
    edwdb11p7
    edwdb11p8

Database Status:
SUCCESS

Viewing the value of Oracle Hidden Paramter

Note:Changing the value of oracle hidden parameters without contacting oracle support would result in unsupported database by ORACLE.


col value format a10
col parameter format a30
set lines 100 pages 0

select a.ksppinm "Parameter",
c.ksppstvl "Value" from x$ksppi a,x$ksppsv c
where a.indx =c.indx
and a.ksppinm like '/_push%' escape '/';( You can substitute the ksppinm value with parameter name you want to view the value for)
 

Installing 11.2.0.2 & creating Database on RHEL5

Installing oracle 11.2.0.2 on file system

Q) Do we need to install Grid infrastructure software even if we plan to use Database file system?--> NO its not required

Select the checkbox if you would like to get security updates

I skipped it

Select if you want to receive software updates, I 've skipped it

I have decided to install oracle binaries (software) & create a database

Single instance DB installation I 've decided to configure database without ASM or RAC options

Installation completed... hopefully this helps.

Connections from client results in ORA-12560

Another encounter with ORA errors, this time it is ORA-12560 when I attempt to connect to database from clients(in my case using TOAD).

"TNS-12560: TNS:protocol adapter error"

Now few blogs do give details about a structured approach to resolve this error. However to begin troubleshooting I wanted to make sure if port 1521 is open.

"telnet" to rescue, this can be used to verify if the port is open.

C:\Documents and Settings\ telnet 192.168.1.200 1521 ---> # telnet ipaddress port number
If this is successful port is open, in my case port was closed so it failed & I was looking to sort this one first.


Server has RHEL installed on it.

As a root user, add the below line before the commit part
# vi /etc/sysconfig/iptables
Append rule as follows:

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT

Save and close the file. Restart iptables:

# /etc/init.d/iptables restart

Run a telnet session to test if port is open now, it was open & I can go ahead & start using clients to connect to Database.


I hope this reaches out to the needy & guides them get over the ORA-12560... Good luck & stay tuned for more... Cheers ..:)