A Problem Record in Oracle Expansion Table Space

Keywords: Linux Oracle Database SQL sqlplus

When this Case happened, I was out of town, and the project manager managed to solve it. I'll sort it out and share it with you.

  • Problem phenomenon
    We need to expand a table space under Oracle DB, but when executing the expansion command, Oracle reported the following error:
    ORA-00059 : maximum number of DB_FILES exceeded
  • Error Reporting Interpretation:
    DB_FILES defines the number of data files in oracle data. When the number of data files exceeds the value set by this parameter, the error ORA-00059 will be reported.
  • Problem handling
    Now that you know the cause of the error, you can start from here and check how big the existing DB_FILES is.
    Login to the database server with sys user and execute show parameter db_files.
    The result is 300. Comparing with the actual one, there are just 300 files, which can't be expanded further.
    Continue executing alter system set db_files=1000 scope=spfile;
    Modify the parameter here to 1000.
    Explanation: The size of the value set by this parameter will not affect efficiency, but simply control the number of data files.
  • New problems arise

    $ sqlplus / as sysdba                                                                           
    SQL> shutdown immediate;
    ORA-24324: service handle not initialized
    ORA-24323: value not allowed
    ORA-01089: immediate shutdown in progress - no operations are permitted
    SQL> shutdown abort;
    ORA-01031: insufficient privileges
    SQL> startup;
    ORA-10997: another startup/shutdown operation of this instance inprogress
    ORA-09968: unable to lock file
    HPUX-ia64 Error: 13: Permission denied
    Additional information: 23564

    When we restart the operation of the repository, we found that it could not stop. From the point of view of error reporting, it seems that the authority has been lost by us.

  • Solution
    1. Reempowering folders
    rx8640a#[/]chown -R oracle:oinstall /u01/app/oracle
    2. Clear memory
    $ ipcs -pmb

    T         ID     KEY        MODE        OWNER     GROUP      SEGSZ  CPID  LPID
    Shared Memory:
    m          0 0x411c09d7 --rw-rw-rw-      root      root        348   943   943
    m          1 0x4e240002 --rw-rw-rw-      root      root      61760   943  8401
    m          2 0x412080da --rw-rw-rw-      root      root       8192   943   945
    m          3 0x00a5c581 --rw-------     sfmdb     users   10469376  2407  2410
    m          4 0x411c05e8 --rw-------      root      root       4096  2680  3291
    m          5 0x06347849 --rw-rw-rw-      root      root      65544  2856  2883
    m   21790726 0x00000000 --rw-r-----    oracle  oinstall 5926617088 10146   263
    m          7 0x0c6629c9 --rw-r-----      root      root   17880696  2894 11235
    m      32776 0x01200452 --rw-rw-r--      root      root       4192  3238  3238
    m          9 0x01200454 --rw-rw-r--      root      root       4192  2924  2924
    m         10 0x01200450 --rw-rw-r--      root      root       4192  2938  2938
    m         11 0x4918031b --rw-r--r--      root      root      22912  2976  2972
    m         12 0x01200448 --rw-rw-r--      root      root       4192  2993  2993
    m         13 0xa9240162 -----------      root      root          1  3230  3232
    m      98318 0x00000000 D-rw-r-----    oracle  oinstall 5939134464 10146   263
    m      98319 0x00000000 D-rw-r-----    oracle  oinstall 8726282240 10146   263
    m      98320 0x00000000 D-rw-r-----    oracle  oinstall      45056 10146   263

    $ ipcrm -m 98318
    $ ipcrm -m 98319
    $ ipcrm -m 98320

    3. Start the database normally:
    SQL> startup;

    ORACLE instance started.
    
    Total System Global Area 2.0552E+10 bytes
    Fixed Size                  2128632 bytes
    Variable Size            9439184136 bytes
    Database Buffers         1.1107E+10 bytes
    Redo Buffers                4259840 bytes
    Database mounted.
    Database opened.

    Then look at the adjusted parameter values, it has reached 1000, this time to perform the expansion table space operation, no problem, ha ha.

Here is an article: http://www.linuxidc.com/Linux...

The key to this Case is to execute ipcs to check the current system resource usage. There are three oracle processes above, so you need to clear them at the system level to start the database properly.

Posted by Kaizard on Tue, 04 Jun 2019 13:23:34 -0700