| PFILE from SPFILE [message #346010] |
Fri, 05 September 2008 10:12  |
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 #346013 is a reply to message #346011] |
Fri, 05 September 2008 10:31  |
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 #346017 is a reply to message #346016] |
Fri, 05 September 2008 10:49 |
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 #346020 is a reply to message #346018] |
Fri, 05 September 2008 11:05 |
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 |
 |
BlackSwan
Messages: 26766
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
