1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

No archive mode data

Discussion in 'Server Administration and Options' started by kimipatel, Sep 19, 2012.

  1. kimipatel

    kimipatel Active Member

    Messages:
    53
    Likes Received:
    1
    Trophy Points:
    140
    Hi,

    My database in No archive mode.


    SQL> show parameter block

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_buffers integer 0
    db_block_checking boolean FALSE
    db_block_checksum boolean TRUE
    db_block_size integer 8192
    db_file_multiblock_read_count integer 8


    SQL> show parameter spfile

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    spfile string %ORACLE_HOME%\DATABASE\SPFILE% ORACLE_SID%.ORA


    SQL> create spfile from pfile;

    File created.

    SQL> show parameter spfile

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    spfile string %ORACLE_HOME%\DATABASE\SPFILE% ORACLE_SID%.ORA


    SQL> alter system set db_file_multiblock_read_count=16;

    System altered.

    SQL> show parameter block

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_buffers integer 0
    db_block_checking boolean FALSE
    db_block_checksum boolean TRUE
    db_block_size integer 8192
    db_file_multiblock_read_count integer 16

    SQL> shut immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 286333852 bytes
    Fixed Size 453532 bytes
    Variable Size 109051904 bytes
    Database Buffers 176160768 bytes
    Redo Buffers 667648 bytes
    Database mounted.
    Database opened.

    SQL> show parameter spfile

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    spfile string %ORACLE_HOME%\DATABASE\SPFILE% ORACLE_SID%.ORA

    SQL> show parameter block

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_buffers integer 0
    db_block_checking boolean FALSE
    db_block_checksum boolean TRUE
    db_block_size integer 8192
    db_file_multiblock_read_count integer 16


    After i create SP file from p file. When i type this command "alter system set db_file_multiblock_read_count=16;" It's shown 16. Than i type command shut immediate. So when i type command start up. than it's show me "db_file_multiblock_read_count integer 16" in stead of 8? What is the problem? Database is in no archive mode that's why or some thing else? Could any one help me out?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    YOU changed it, in both memory and the spfile. By default 'alter system' statements affect BOTH memory and the spfile as SCOPE is set to BOTH if you don't set it explicitly. It has NOTHING to do with archivelog mode (a read of the Concepts Guide is in order, I think). Since you modified the spfile when you restarted Oracle it read the NEW value of 16 and used it. Had you typed:

    alter system set db_file_multiblock_read_count=16 scope=memory;

    it would have affected ONLY the instance that was running and NOT changed the spfile. Thus when you would have restarted Oracle the spfile would have been unaltered and you'd have seen db_file_multiblock_read_count return to 8.

    The online documentation at http://tahiti.oracle.com explains this.
     
  3. kimipatel

    kimipatel Active Member

    Messages:
    53
    Likes Received:
    1
    Trophy Points:
    140
    No. I didn't type above command. As i mention above things. that's only i typed. Nothing else. So i didn't typed both. So how it was set 16? I just created file from p file.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Obviously the original explanation is lost on you; I shall try again.

    BY DEFAULT (which means Oracle does this whether or NOT you type in the full command syntax) the 'alter system ' instructions affect BOTH the currently running instance AND the spfile. YOU DO NOT NEED TO TYPE IN THE WORD "BOTH" FOR THIS TO HAPPEN. You changed the spfile AND the currently running instance when you typed in 'alter system set db_file_multiblock_read_count=16;'. Restarting the database read the CHANGED spfile and set db_file_multiblock_read_count accordingly. Here is the record of what YOU did:

    "SQL> create spfile from pfile;

    File created.

    SQL> show parameter spfile

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    spfile string %ORACLE_HOME%\DATABASE\SPFILE% ORACLE_SID%.ORA


    SQL> alter system set db_file_multiblock_read_count=16;

    System altered.

    SQL> show parameter block

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_buffers integer 0
    db_block_checking boolean FALSE
    db_block_checksum boolean TRUE
    db_block_size integer 8192
    db_file_multiblock_read_count integer 16

    SQL> "

    It has NOTHING to do with archivelog mode.


    David Fitzjarrell