Database Reference
In-Depth Information
reCOVerING FrOM SettING a BaD SpFILe paraMeter
take care not to set the
LOG_ARCHIVE_FORMAT
to an invalid value; for example,
SQL> alter system set log_archive_format='%r_%y_%dk.arc' scope=spfile;
if you do so, when you attempt to stop and start your database, you won't even get to the nomount phase
(because the
spfile
contains an invalid parameter):
SQL> startup nomount;
ORA-19905: log_archive_format must contain %s, %t and %r
in this situation, if you're using an
spfile
, you can't start your instance. the easiest thing to do at this point is to
create a text based
init.ora
file from the contents of the
spfile
. you can use the linux/unix
strings
command
to accomplish this:
$ cd $ORACLE_HOME/dbs
$ strings spfile$ORACLE_SID.ora
the prior command will extract the text out of the binary
spfile
and display it on your screen. you can then cut
and paste that text into an
init.ora
file and use that to start your database. if you're using Windows, you can
use a utility such as
write.exe
to display the text in a binary file.
When you specify
LOG_ARCHIVE_FORMAT
, you must include
%t
(or
%T
),
%s
(or
%S
), and
d%
in the format string.
Table
2-1
lists the valid variables you can use with the
LOG_ARCHIVE_FORMAT
initialization parameter.
Table 2-1.
Valid Variables for the Log Archive Format String
Format String
Meaning
%s
Log sequence number
%S
Log sequence number padded to the left with zeros
%t
Thread number
%T
Thread number padded to the left with zeros
%a
Activation ID
%d
Database ID
%r
Resetlogs
ID required to ensure uniqueness
across multiple incarnations of the database
You can view the value of the
LOG_ARCHIVE_DEST_N
parameter by running the following:
SQL> show parameter log_archive_dest