Home » RDBMS Server » Backup & Recovery » How can I restore parameter file ? (Oracle 10gR2 on RHEL 4 )
How can I restore parameter file ? [message #454869] Fri, 07 May 2010 20:53 Go to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
While attempting to do a TIME-BASED database recovery, I made a mistake and set the nls_date_format parameter as follows:

SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:si' scope=spfile;

System altered.

SQL> shutdown immidiate
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-01821: date format not recognized

I tried to fix it by editing the legible entry in spfileorcl.ora
but I think that was a bad idea and maybe corrupted the file. The reason I think it is corrupted is because when I try to startup the database now I get:

[oracle@jhlinux labs2]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 7 21:17:09 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect sys / as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora'


I beleive I had setup the control file and spfile to be backed up automatically but I am not sure what to do with the files I see :

[oracle@jhlinux autobackup]$ pwd
/u01/app/oracle/flash_recovery_area/ORCL/autobackup
[oracle@jhlinux autobackup]$ ll
total 32
drwxr----- 2 oracle oinstall 4096 May 1 15:07 2010_04_27
drwxr----- 2 oracle oinstall 4096 May 1 15:07 2010_04_30
drwxr-x--- 2 oracle oinstall 4096 May 1 15:07 2010_05_01
drwxr-x--- 2 oracle oinstall 4096 May 2 19:06 2010_05_02

Please advise and help. Thanks in advance.
Re: How can I restore parameter file ? [message #454870 is a reply to message #454869] Fri, 07 May 2010 21:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I tried to fix it by editing the legible entry in spfileorcl.ora
NEVER edit any spfile!

>ORA-01078: failure in processing system parameters
>LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora'


109, 0, "could not open parameter file '%.*s'"
// *Cause: The parameter file does not exist.
// *Action: Create an appropriate parameter file.


create new pfile from alert_SID.log
then do
sqlplus
/ as sysdba
startup initSID.ora
exit

[Updated on: Fri, 07 May 2010 21:14]

Report message to a moderator

Re: How can I restore parameter file ? [message #454879 is a reply to message #454869] Sat, 08 May 2010 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can change the values inside spfile in nomount state.

If you drop it you can restore with rman command:
restore spfile from autobackup;

Regards
Michel
Re: How can I restore parameter file ? [message #455024 is a reply to message #454870] Sun, 09 May 2010 23:38 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Sir,

I am now getting the following error message after creating a pfile from alert_SID.log

SQL> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora
LRM-00116: syntax error at 'dispatchers' following '='
ORA-01078: failure in processing system parameters

the entry in question is in bold below. I tried single quotes and double quotes to no avail. Please advise

.
.
.
db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size = 10737418240
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 172800
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers =(PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes = 10
background_dump_dest = '/u01/app/oracle/admin/orcl/bdump'
user_dump_dest = '/u01/app/oracle/admin/orcl/udump'
core_dump_dest = '/u01/app/oracle/admin/orcl/cdump'
audit_file_dest = '/u01/app/oracle/admin/orcl/adump'
.
.
.
Re: How can I restore parameter file ? [message #455027 is a reply to message #455024] Mon, 10 May 2010 00:06 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.orafaq.com/forum/t/155466/0/
check your sysntax errors

sriram Smile
Re: How can I restore parameter file ? [message #455028 is a reply to message #455027] Mon, 10 May 2010 00:13 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
found it.

Wrong syntax:

dispatchers =(PROTOCOL=TCP) (SERVICE=orclXDB)

Correct syntax for dedicated mode:

"dispatchers =(PROTOCOL=TCP) (SERVICE=orclXDB)"


This is very obscure if you are not familiar with pfile .

Thanks to all for pointing in right direction.
Re: How can I restore parameter file ? [message #455268 is a reply to message #454870] Mon, 10 May 2010 21:30 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Hi guys,

I am now in my database and at this point I think it wise to restore my spfile from autobackup. Using rman I need to be in mount status which is where I am.

SQL> select status from v$instance;

STATUS
------------
MOUNTED


Now when I try to enter rman connecting to the catalog database I am getting the following error:

oracle@jhlinux admin]$ rman target / catalog sys/oracle@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 10 22:19:30 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1242703296, not open)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


Please advise.
Re: How can I restore parameter file ? [message #455269 is a reply to message #455268] Mon, 10 May 2010 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Please advise.
You have the classic chicken & egg problem.
RMAN uses SQL*Net to connect to instance.
Instance needs to be up so it can register with listener, so you can connect to instance to bring it up.

The solution is to change listener.ora so that listener "knows" about instance while instance is not yet up.
Re: How can I restore parameter file ? [message #455270 is a reply to message #455268] Mon, 10 May 2010 22:19 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
I managed to get in by doing:

$rman

RMAN> connect target

RMAN> restore spfile from autobackup;


Now my spfile has been restored.

Thanks to you all.
Re: How can I restore parameter file ? [message #455285 is a reply to message #455270] Mon, 10 May 2010 23:48 Go to previous message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good! but you'd not lost 4 days if you tried it just after I posted this. Wink
Thanks for the feedback.

Regards
Michel
Previous Topic: Restore backup to Fresh Database
Next Topic: Incremental Backups
Goto Forum:
  


Current Time: Mon May 20 14:44:03 CDT 2024