Home » RDBMS Server » Server Administration » PFILE from SPFILE (Oracle 10.2.0.3, Solaris 10)
PFILE from SPFILE [message #346010] Fri, 05 September 2008 10:12 Go to next message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
I have a 2-node RAC running in passive-active mode. It was originally active passive, but after node one went down, it became passive-active (node1-node2).

I am now trying to get it back in a situation where its active-passive (node1-node2) due to some applications by nature using node-2 since ever even when node-2 was passive, and now since node-2 is active, everything is on node-2 and node-1 is sitting idle.

To do this, I tried to first change a parameter in SPFILE called active_instance_count to value 1. Upon doing that (did from TOAD), it says system altered. But when I go see the parameter file, it shows no value whereas it should show 1 as the system was altered.

Soon after I figured out that the active_instance_count parameter is not system modifiable. So I'd have to:

1- CREATE PFILE FROM SPFILE,
2- edit [B]active_instance_count[/B] with value = 1
3- Shutdown Database
4- STARTUP PFILE='path'
5- CREATA SPFILE FROM PFILE


Strange thing happened, is that as soon as I did the step number one, I see following entry in my PFILE:

*.active_instance_count=1


Also,
SQL> select value, isdefault from v$sys_optimizer_env where name = 'active_instance_count';

VALUE                     ISD
------------------------- ---
2                         YES


How come it shows me a value of 1 whereas it was in passive mode? Through TOAD, it shows me no values in parameter file on both nodes, so I can't tell which one is active and which one is passive until I go see the CPU utilization.

Q1: So, what does *. mean in the beginning of the parameter?
Q2: how come TOAD showed me system altered if its not system modifiable?
Q3: How can I see on the go as to which one is Active or Passive?
Q4: After doing the five-steps I have mentioned above, how would I know that node-1 is active mode?

Thanks in advance.

[Updated on: Fri, 05 September 2008 10:23]

Report message to a moderator

Re: PFILE from SPFILE [message #346011 is a reply to message #346010] Fri, 05 September 2008 10:23 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


It would be so much better for us, if you used SQL*Plus along with CUT & PASTE so we could actually see for ourselves exactly what you did & how oracle responded.

>Q1: So, what does *. mean in the beginning of the parameter?
The asterisk means this parameter is applied to all nodes in the cluster

Please post the contents of your pfile.
Re: PFILE from SPFILE [message #346013 is a reply to message #346011] Fri, 05 September 2008 10:31 Go to previous messageGo to next message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
Thanks for your quick reply.

Quote:
It would be so much better for us, if you used SQL*Plus along with CUT & PASTE so we could actually see for ourselves exactly what you did & how oracle responded.


I mentioned in my first post that, altering the system was done through toad. And you know the way TOAD works. It just says "system altered" at the bottom of the screen.

But whatever, I did from the SQL*PLUS, I have pasted that as is. Please tell me what exactly do you want me to do?

So, does *.active_instance_count=1 mean that both my nodes are active?

Do I have to remove *. from the beginning of the file in order to make it an active-passive?

PROD1.__db_cache_size=289406976
PROD2.__db_cache_size=289406976
PROD1.__java_pool_size=125829120
PROD2.__java_pool_size=125829120
PROD1.__large_pool_size=4194304
PROD2.__large_pool_size=4194304
PROD1.__shared_pool_size=415236096
PROD2.__shared_pool_size=415236096
*._gc_affinity_time=0
*._recyclebin=FALSE
*.active_instance_count=1
PROD1.active_instance_count=1
*.background_dump_dest='/u01/app/oracle/admin/PROD/bdump'
*.cluster_database_instances=2
*.cluster_database=TRUE
*.compatible='10.1.0.2.0'
*.control_files='+PROD_DATA1/prod/controlfile/current.260.3'
*.core_dump_dest='/u01/app/oracle/admin/PROD/cdump'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_create_file_dest='+PROD_DATA1'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='PROD'
*.db_recovery_file_dest_size=214748364800
*.db_recovery_file_dest='+PROD_FLASH1'
*.db_writer_processes=2
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
PROD2.instance_number=2
PROD1.instance_number=1
*.java_pool_size=125829120
*.job_queue_processes=10
*.log_archive_dest_2='location=/u02/archives'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=300
*.remote_listener='LISTENERS_PROD'
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.sga_target=838860800
*.smtp_out_server='172.16.10.176:25'
*.sort_area_size=65536
PROD2.thread=2
PROD1.thread=1
*.undo_management='AUTO'
PROD1.undo_retention=900
PROD2.undo_tablespace='UNDOTBS2'
PROD1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/PROD/udump'
Re: PFILE from SPFILE [message #346016 is a reply to message #346010] Fri, 05 September 2008 10:41 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>And you know the way TOAD works.
Since I do not (ab)use TOAD, I do not know how it works.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams007.htm#sthref42
From above "Real Application Clusters You must set this parameter for every instance, and multiple instances must have the same value."

You should RTFM above, correct your pfile/spfile, shutdown both node & restart PROD1 first.
Re: PFILE from SPFILE [message #346017 is a reply to message #346016] Fri, 05 September 2008 10:49 Go to previous messageGo to next message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
I did RTFM before posting the question. But that FM doesn't help. It just says value on multiple instance should be same.

If you RTFM before sending the link to me, you would know that I can't have the same value on all instances, I am trying to do an active-passive here.

Secondly, I can not restart node-2 as all production is being taken care of on that node.

So, what now?

Also, I suppose that by just creating a PFILE from SPFILE doesn't shift the system pointer to PFILE. Correct?
Re: PFILE from SPFILE [message #346018 is a reply to message #346010] Fri, 05 September 2008 10:54 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
"When you set this parameter to 1, the first instance you start up becomes the primary instance and accepts client connections. The second instance starts up as a secondary instance and can accept client connections only if the first instance fails. In such an event, the secondary instance becomes the primary instance.

When the failed instance can once again be started up, it starts up as the secondary instance, and will not accept client connections unless the current primary instance fails."

PROD1 was primary.
PROD1 failed.
PROD2 became primary.
PROD1 came back to life & joined cluster as secondary.
Please note the part above which states "will not accept client connections unless the current primary instance fails."
The way to get PROD1 to be primary is to pull the plug on PROD2.
After PROD2 fails, PROD1 will once again become primary.
Re: PFILE from SPFILE [message #346020 is a reply to message #346018] Fri, 05 September 2008 11:05 Go to previous messageGo to next message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
I liked Pull the Plug part.

True, I read that statement somewhere but didn't emphasis on it much, eventually not knowing that was the only way to switch over.

But then, knowing the fact that active_instance_count = 0 means passive and 1 means active, how can I trust that by taking the node-2 down and then bringing back up won't make it an active node given the fact that it also has active_instace_count set to 1?

One of the questions in my most recent post was:
Quote:
Also, I suppose that by just creating a PFILE from SPFILE doesn't shift the system pointer to PFILE. Correct?

Is that correct? or do I have to restore the old PFILE back which contains only one line and that is SPFILE location?

Thanks
Re: PFILE from SPFILE [message #346022 is a reply to message #346010] Fri, 05 September 2008 11:14 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
As with many this involving Oracle, it depends upon multiple factors.
In and of itself creating a pfile won't impact working nodes in a cluster.
If you wish to ensure no impact in the future, you can either delete the recently created pfile or simply rename it out of the way.
I am not sure how well documented the follow RAC behavior is documented.
A RAC can be managed via the following single file (assuming $ORACLE_HOME is on shared clustered file system):
$ORACLE_HOME/dbs/spfile.ora
If this file exists, it will be used before any other spfile or pfile.
by using truss (or strace on Linux) you can confirm which files sqlplus tries to open when given the STARTUP command.

[Updated on: Fri, 05 September 2008 11:15] by Moderator

Report message to a moderator

Re: PFILE from SPFILE [message #346030 is a reply to message #346022] Fri, 05 September 2008 11:53 Go to previous message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
You want something good?

Here it is:
#To relocate a named service member from crm1 to crm3:
srvctl relocate service -d crm -s crm -i crm1 -t crm3

Option  Description 
------  -----------
-d      db_unique_name Unique name for the database. 
-s      service_name Service name. 
-i      old_inst_name Old instance name. 
-t      new_inst_name New instance name. 
-f      Disconnect all sessions during stop or relocate service operations. 


You don't need to restart or play with active_instance_count.

Thanks for your help.
Pinky
Previous Topic: upgradation to 10.1.0.2.0 from 9.2.0.1.0
Next Topic: Data masking
Goto Forum:
  


Current Time: Wed Dec 07 20:36:04 CST 2016

Total time taken to generate the page: 0.12920 seconds