Home » Server Options » RAC & Failsafe » Pfile or Spfile issue (10G RAC windows 2003)
Pfile or Spfile issue [message #428866] Fri, 30 October 2009 06:49 Go to next message
mamingui
Messages: 83
Registered: July 2006
Member
Hi all,

I've a lot problem in my RAC DB

See below :


C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Oct 30 12:14:04 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> conn /as sysdba
Connected.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount pfile='mvdbcgpfile.ora';
ORACLE instance started.
Total System Global Area 6442450944 bytes
Fixed Size 2064536 bytes
Variable Size 1090522984 bytes
Database Buffers 5335154688 bytes
Redo Buffers 14708736 bytes

SQL> alter database mount;
Database altered.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 780
Next log sequence to archive 781
Current log sequence 781

SQL> alter system switch logfile;
System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 781
Next log sequence to archive 782
Current log sequence 782

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string M:\
db_recovery_file_dest_size big integer 999147483648

SQL> show parameter log_archive_format;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string Arch%t_%s_%r
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> create spfile from pfile='mvdbcgpfile.ora';

File created.

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size 2064536 bytes
Variable Size 1090522984 bytes
Database Buffers 5335154688 bytes
Redo Buffers 14708736 bytes
Database mounted.
Database opened.
SQL> show parameter log_archive_format;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %s_%t_%r
SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string M:\
db_recovery_file_dest_size big integer 999147483648
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size 2064536 bytes
Variable Size 1090522984 bytes
Database Buffers 5335154688 bytes
Redo Buffers 14708736 bytes
Database mounted.
Database opened.
SQL>

1) the archived log are not sent int M:\ but in M:\MVDBCG\ARCHIVELOG\2009_10_30\
With another format (example : 01_MF_1_776_5GODO2N0_.ARC)

What I can do to make the system using correctly my pfile ?

2) I've a RMAN catalog and I cannot backup archived logs because the system is still looking for the old folder where I was keeping archived logs. It seems to that it doesn't care about the new destination set in the pfile.

Someone can help me ? please find atteched my pfile.

Thanks in advance.
Re: Pfile or Spfile issue [message #428869 is a reply to message #428866] Fri, 30 October 2009 07:23 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

SQL> create spfile from pfile='mvdbcgpfile.ora';

File created.

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.


1. After create spfile again create pfile from spfile.

2. As per your requirement change db setting using

ALTER SYSTEM SET <<DB PARAMETER>> = <<VALUE>> SCOPE=SPFILE/PFILE SID=*;


- Babu
Re: Pfile or Spfile issue [message #428870 is a reply to message #428866] Fri, 30 October 2009 07:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You never respond to the old postings with what we asked you to do.
Instead keep opening new threads with newer issues.
Should this continue, there is no point in trying to respond your posts.
As said before multiple times, fix your SPILE.

>>create spfile from pfile='mvdbcgpfile.ora';
will create spfile in $ORACLE_HOME/dbs instead of in shared area.
use
create spfile='+myASMarea/../' from pfile='/path';

shutdown.
Now edit the initsid.ora in all nodes, remove the entries and add

SPFILE='/path_to_spfile'

This way, both init.ora in both nodes will now point to same spfile in asm.
Now startup individually or using srvctl

[Updated on: Fri, 30 October 2009 07:27]

Report message to a moderator

Re: Pfile or Spfile issue [message #428881 is a reply to message #428866] Fri, 30 October 2009 08:24 Go to previous messageGo to next message
mamingui
Messages: 83
Registered: July 2006
Member
Thank you for your help but It doesn't work.

So, I've one question : I think when I'm using oracle parameter like USE_DB_RECOVERY_FILE_DEST I cannot specify a log_archive_format and Flash Recovery Area Location is use but completing by oracle's folders
Re: Pfile or Spfile issue [message #428890 is a reply to message #428866] Fri, 30 October 2009 09:30 Go to previous messageGo to next message
mamingui
Messages: 83
Registered: July 2006
Member
Excuse me Mahesh,

I just read your mail now.
I've tried your idea and It's working.
But I'm obliged now to start my instance one by one using this syntax :

startup pfile='mvdbcgpfile.ora';

is it possible to start it a automatically using just a STARTUP command ?

Regards
Re: Pfile or Spfile issue [message #428892 is a reply to message #428890] Fri, 30 October 2009 09:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
startup from sql*plus will start only the local instance identified by ORACLE_SID.
Try using srvctl.
Provided you have configured your RAC properly srvctl should work.
Re: Pfile or Spfile issue [message #428893 is a reply to message #428890] Fri, 30 October 2009 09:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Also,
>>I've tried your idea and It's working.
>> but It doesn't work.
is not a clear response ( apart from fact that we have no Idea whom you are responding to or to which post) as there are several issues that are addressed.
Quote and respond.
Explain/post session for what is not working.
And please use CODE tags.

[Updated on: Fri, 30 October 2009 09:40]

Report message to a moderator

icon9.gif  Re: Pfile or Spfile issue [message #428899 is a reply to message #428866] Fri, 30 October 2009 10:15 Go to previous messageGo to next message
mamingui
Messages: 83
Registered: July 2006
Member
Thank you Mahesh, It is not easy for me because I don't speak english but French !!
And I'm trying to explain as clearly as possible my problem. Thank you again for your help and please find below the output when I try to start database using srvctl

C:\>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Oct 30 16:04:41 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup pfile='mvdbcgpfile.ora';
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size 2064536 bytes
Variable Size 1073745768 bytes
Database Buffers 5351931904 bytes
Redo Buffers 14708736 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64
bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

C:\>srvctl stop database -d mvdbcg;

C:\>srvctl status database -d mvdbcg;
Instance MVDBCG1 is not running on node cgtierdb01
Instance MVDBCG2 is not running on node cgtierdb02

C:\>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Oct 30 16:09:04 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> exit
Disconnected

C:\>srvctl start database -d mvdbcg;
PRKP-1001 : Error starting instance MVDBCG1 on node cgtierdb01
CRS-0215: Could not start resource 'ora.MVDBCG.MVDBCG1.inst'.
PRKP-1001 : Error starting instance MVDBCG2 on node cgtierdb02
CRS-0215: Could not start resource 'ora.MVDBCG.MVDBCG2.inst'.

C:\>
Re: Pfile or Spfile issue [message #428900 is a reply to message #428899] Fri, 30 October 2009 10:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I don't speak english
Same for most of folks here. I am bilingual at home and English is not among the two. Language is not a barrier here.
Please use CODE and QUOTE appropriately for better readability.
See the difference between your code and ours (mine and gentlebabu).

First, make sure your SPFILE is working.
Do this in your local node1.
export ORACLE_SID=myInstance1.ora
sqlplus / as sysdba
startup

If above works for both nodes (with appropriate ORACLE_SID), spfile is visible on both nodes.

On each node, $ORACLE_HOME/log/name_of_node/racg directory will
have several log files.
First look into node1 and post only the relevant, latest error messsage (instead of whole file).
Particularly post the latest messages from
$ORACLE_HOME/log/your_host/racg/ora.database_name.instance_name1.inst.log

In your case it would be it
$ORACLE_HOME/log/cgtierdb01/racg/ora.MVDBCG.MVDBCG1.inst.log  
and
$ORACLE_HOME/log/cgtierdb01/racg/imon<yourDBname>.log

Post only last few relevant lines
Re: Pfile or Spfile issue [message #428902 is a reply to message #428899] Fri, 30 October 2009 10:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I believe you still have issues with spfile.
Start both instances manually (using sqlplus ) from specific nodes and look for spfile in ASM.
Re: Pfile or Spfile issue [message #428905 is a reply to message #428866] Fri, 30 October 2009 11:14 Go to previous messageGo to next message
mamingui
Messages: 83
Registered: July 2006
Member
C:\>export ORACLE_SID=mvdbcg1.ora
'export' is not recognized as an internal or external command,
operable program or batch file.

C:\>set ORACLE_SID=mvdbcg1.ora

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Oct 30 17:04:21 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> conn / as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error


SQL> conn sys@mvdbcg1 as sysdba
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SQL> exit

C:\>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Oct 30 17:05:59 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name: sys@mvdbcg1 as sysdba
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:
Re: Pfile or Spfile issue [message #428906 is a reply to message #428866] Fri, 30 October 2009 11:16 Go to previous messageGo to next message
mamingui
Messages: 83
Registered: July 2006
Member
2nd file
Re: Pfile or Spfile issue [message #428907 is a reply to message #428905] Fri, 30 October 2009 12:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
First,
set ORACLE_SID=mvdbcg1.ora

that was my mistake.
Sorry about that.
It is supposed to be (in node1 and mvdbcg2 in node 2)

set ORACLE_SID=mvdbcg1


From logs
 SQL> ORA-32004: obsolete and/or deprecated parameter(s) specified


Quote:
db_recovery_file_dest string M:\

Is that a real shared area?
Seems not.
Create FRA inside asm and ignore log_archive_format.

Even before attempting to backup RAC/enable archiving, I would first stabilize the rac.
Remove all archive logging, log destinations, FRA etc.
Just try to open the database in RAC mode without these.
Later you can add them.
Re: Pfile or Spfile issue [message #428909 is a reply to message #428907] Fri, 30 October 2009 12:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
set ORACLE_SID=mvdbcg1

Is that your ORACLE_SID?
In unix, it is case sensitive. Not sure about windows.
Re: Pfile or Spfile issue [message #428923 is a reply to message #428866] Fri, 30 October 2009 15:35 Go to previous message
mamingui
Messages: 83
Registered: July 2006
Member
Thanks Mahesh

For node1, instance name = mvdbcg1
For node2, instance name = mvdbcg2
global instance name = mvdbcg

M:\ are in SAN storage.

You're right, I need to stabilize RAC before.
Previous Topic: Suggestion to store clusterware files
Next Topic: CRS-1028 and CRS-0223 errors
Goto Forum:
  


Current Time: Thu Mar 28 07:32:30 CDT 2024