A startup parameter error calls for a correction that can be traced, tested, and reversed. Preserve the original configuration, identify the exact rejected setting, review a separate text file, and test the intended instance. Creating a replacement file and selecting it for the next startup are separate actions.
Oracle DBA Lesson 23 Part 2 — Plan Recovery from a Bad Startup Parameter
Find the cause in the error stack
A failing copied PFILE, init_trial.ora, contains this invalid entry:
*.processes='four_hundred'
A corresponding diagnostic example is:
ORA-01078: failure in processing system parameters
LRM-00104: 'four_hundred' is not a legal integer for 'processes'
ORA-01078 identifies parameter processing as the failed startup step. The detailed LRM-00104 entry identifies the rejected text and the parameter requiring an integer. Read and retain the entire actual stack. Another error may have a different cause or require another procedure. Do not apply this example to file loss, media recovery, or unrelated startup failures.
PROCESSES is an integer process-capacity setting. Its chosen value must support required Oracle background processes and the intended workload. Four hundred is not a universal sizing recommendation. Associated derived settings such as SESSIONS and TRANSACTIONS also need evaluation when resizing.
The example deliberately places the invalid words in a copied text file. It does not claim that an ordinary parameter-change command accepted these words into an SPFILE.
See ORA-01078, LRM-00104, and PROCESSES.
Preserve the original and review a separate correction
Record the exact original SPFILE path and startup arrangement. Keep that original file intact. Begin with the previously validated text export and save a separate reviewed file, for example /u01/private_config/init_reviewed.ora.
The relevant change is:
-*.processes='four_hundred'
+*.processes=400
Retain the diff and verify every changed line. Preserve the other valid configuration entries. One shown setting is only a fragment, not a complete startup file. Check instance-specific settings, units, file paths, and the actual intended capacity. Use an unused private filename rather than replacing the original SPFILE or the only validated text copy. A binary SPFILE must be managed through Oracle's parameter and file mechanisms.
Test the named file on the intended stopped instance
Confirm the intended host, Oracle home, ORACLE_SID, administrative authentication, and stopped-instance state. Do not use FORCE to bypass an unexpected running instance.
STARTUP NOMOUNT PFILE='/u01/private_config/init_reviewed.ora'
PFILE selects the specific text source read by SQL*Plus. The file is on the same host as the server. Remote SQL*Plus may read a client-side PFILE, so resolve its location for the actual operation.
NOMOUNT creates the instance and leaves the database unmounted. It is a useful scoped test of whether this corrected parameter source allows the intended instance to start. Mounting, opening, and service availability require additional checks.
Inspect the target and stage:
SELECT instance_name, host_name, status
FROM v$instance;
For a successful test, a row is:
INSTANCE_NAME HOST_NAME STATUS
APP DB_HOST STARTED
Match INSTANCE_NAME and HOST_NAME against the intended target. STARTED is the documented status after STARTUP NOMOUNT. It establishes that stage. Do not treat a connection alone as target verification.
Confirm the effective corrected value:
SELECT name, display_value
FROM v$system_parameter
WHERE name = 'processes';
The expected value is 400. Resolve an unexpected value or target before proceeding. The test uses a PFILE. A nonempty active-SPFILE path is not expected for this direct text-source test.
See SQL*Plus STARTUP, V$INSTANCE, and V$SYSTEM_PARAMETER.
Create a separately named binary candidate
After reviewing the test evidence, create a new file:
CREATE SPFILE='/u01/private_config/spfile_candidate.ora'
FROM PFILE='/u01/private_config/init_reviewed.ora';
FROM PFILE selects the reviewed source. Both the input PFILE for this SQL statement and the output SPFILE reside on the database server. The server OS account needs appropriate read and write permission. The directory must already exist. Use a new destination and preserve its restrictive access policy. This is a different file-location rule from a remote SQL*Plus STARTUP PFILE.
The statement creates the binary candidate. It can run before or after instance startup with the documented administrative privilege. In a CDB the operation belongs to the root with a commonly granted privilege. It cannot overwrite the same SPFILE used by an already running instance. Omitting the output filename can overwrite the platform default SPFILE, so name a separate candidate explicitly.
Managed-resource exception: when the database is registered in Clusterware or Oracle Restart, a qualifying CREATE SPFILE ... FROM PFILE can automatically update the database resource's SPFILE path. AS COPY prevents that resource update when applicable. Do not copy this candidate-creation sequence into a managed database. Use its resource-aware procedure and verify the resource configuration.
See CREATE SPFILE.
Decide how startup will discover the candidate
For unmanaged SQL*Plus, normal startup checks these default names in the platform's default parameter-file location:
spfile<SID>.oraspfile.orainit<SID>.ora
Replace <SID> with the actual instance identifier. Angle brackets are descriptive placeholders. The new private candidate path is outside this default route. File existence alone does not select it.
A one-line pointer PFILE can select a nondefault SPFILE. Put the following in a new file such as /u01/private_config/init_pointer.ora:
SPFILE='/u01/private_config/spfile_candidate.ora'
After stopping the test instance, the pointer can be used explicitly:
STARTUP NOMOUNT PFILE='/u01/private_config/init_pointer.ora'
SQL*Plus reads the pointer file and passes the named SPFILE location to the server, which reads the binary candidate. A pointer is a reference, not a physical move or deletion of the candidate. IFILE is not the parameter for pointing to a binary SPFILE.
This explicit pointer test is still a NOMOUNT test. To make a later plain STARTUP use an intended source, the default-file or pointer arrangement must select it, taking the earlier SPFILE search precedence into account. If Oracle Restart or Clusterware controls startup, inspect and manage its SPFILE resource instead. ASM and RAC require their own storage and cluster context.
See startup discovery and nondefault SPFILE pointers.
Validate the selected startup and retain rollback
After a startup using the chosen source, inspect:
SHOW PARAMETER spfile
SELECT instance_name, host_name, status FROM v$instance;
SELECT name, display_value FROM v$system_parameter
WHERE name = 'processes';
Confirm the active SPFILE path names the intended candidate, the target identity is correct, and the effective setting is the reviewed value. Then complete the database-opening, required PDB/service, and application-connection tests.
Retain the original configuration and its discovery arrangement until acceptance checks support replacement. A rollback plan identifies the original source, the stopped-instance transition, the required owner, the original pointer or resource configuration, restart checks, and service validation. Restoring a filename alone does not verify availability.
Recap
Preserve the original SPFILE and startup arrangement, and read the whole error stack before changing anything. ORA-01078 identifies parameter processing as the failed startup step. LRM-00104 names the rejected integer and the parameter. Review a separate corrected PFILE, keep the diff, and treat one displayed line as a fragment rather than a complete startup file. On the intended stopped instance, STARTUP NOMOUNT PFILE tests whether that named text source can start the instance. Match INSTANCE_NAME and HOST_NAME, confirm status STARTED, and confirm the effective PROCESSES value. CREATE SPFILE ... FROM PFILE writes a separately named binary candidate on the server. File creation does not make unmanaged plain STARTUP select an arbitrary new path. The default names spfile<SID>.ora, spfile.ora, and init<SID>.ora, or an explicit pointer PFILE, must select it. When the database is registered in Clusterware or Oracle Restart, a qualifying create can update the resource SPFILE path, and AS COPY prevents that update when applicable. Keep this sequence on an unmanaged instance. Retain the original until acceptance checks support replacement.
No comments:
Post a Comment