-1

I have been getting this error since I tried to the following command:

alter system set sga_target = 1512m scope=spfile;

I get the following message when I try type any command in sqlplus:

SQL> ALTER SYSTEM SET SGA_max_size = 1512m scope = spfile;
ALTER SYSTEM SET SGA_max_size = 1512m scope = spfile
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

Is there any possible solutions to this problem?

6
  • Did you do a connect / as sysdba first?
    – Philᵀᴹ
    Mar 29, 2017 at 12:23
  • yes I did connect as connect / as sysdba
    – Russkiy
    Mar 29, 2017 at 12:25
  • Is the database open? ALTER DATABASE OPEN
    – John K. N.
    Mar 29, 2017 at 12:41
  • Is there an SPFILE or is the Oracle instance running on the INIT file?
    – John K. N.
    Mar 29, 2017 at 12:41
  • There is an SPFILE stored ,I can see it one of the folders .Everything was working fine until I decided to execute the following command alter system set sga_target = 1512m scope=spfile; ,then I started to get the following errors, I think the problem is that the MEMORY_TARTGET is too small but it won't let me increase it
    – Russkiy
    Mar 29, 2017 at 12:49

1 Answer 1

4

Please ensure you use the correct names for your PFILE and SPFILE. Backup both files before you start playing around with them

Check Instance

Start SQL*Plus (Windows) or sqlplus (*nix):

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 29 15:09:29 2017

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

Connected to an idle instance.

If you are connected to an idle instance, then the instance has probably not been started (maybe due to the low SGA setting).

As a precautionary measure shutdown your instance with ABORT to ensure the instances goes down fast:

SQL> shutdown abort

Restart database instance step by step

Then restart the database instance step by step and watch for any issues.

  1. Startup SQL Plus without logging in:

    bash> sqlplus /nolog
  2. Connect to the database instance:

    SQL> connect  as SYSDBA
  3. Start the instance using the PFILE (editable settings) instead of the SPFILE. You can modify the SGA settings in the PFILE before you start the instance:

    SQL> STARTUP PFILE = /u01/oracle/dbs/pfile_.ora
  4. Check the settings for SGA:

    SQL> show sga

    You should get something like this:

    Total System Global Area 2563887104 bytes
    Fixed Size                  2242504 bytes
    Variable Size             830474296 bytes
    Database Buffers         1723858944 bytes
    Redo Buffers                7311360 bytes
  5. If the instance can start with the pfile then you can create an SPFILE from the PFILE with the following command:

    SQL> create spfile = '/u01/oracle/dbs/spfile_.ora' from pfile = '/u01/oracle/dbs/pfile_.ora' 

    Continue with step 7.

  6. If the instance can not start with the PFILE then you have to create a new one with the following command:

    SQL> create pfile = '/u01/oracle/dbs/pfile_.ora' from spfile = '/u01/oracle/dbs/spfile_.ora' 

    Modify the SGA settings in the PFILE and start over with the startup procedure at step 1.

  7. Shutdown the instance again and restart:

    SQL> Shutdown immediate
  8. followed by:

    SQL> startup

Your instance should be back up and running.

2
  • Thank you for the detailed answer but I can't seem to find an PFILE,I'm stuck at the SQL> STARTUP PFILE = /u01/oracle/dbs/pfile_.ora ,I think will just reinstall the database
    – Russkiy
    Mar 29, 2017 at 19:54
  • Well you can create a PFILE from the existing SPFILE and then modify the PFILE manually as outlined in step 6.
    – John K. N.
    Mar 30, 2017 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.