oracle parameters pfile spfile - ghdrako/doc_snipets GitHub Wiki

Wyświetl niestandardowe i przestarzałe parametry

SELECT name
FROM v$obsolete_parameter
WHERE isspecified = 'TRUE';

SELECT name, value
FROM v$system_parameter
WHERE isdefault = 'FALSE';

select value from v$parameter where name = 'db_block_size';

show parameter db_block_s

SHOW PARAMETER adds % to the front and back.

“Normal” accounts are not granted access to the V$ performance views by default. You’ll have to grant select on V$PARAMETER to SCOTT.

$ sqlplus / as sysdba 
SQL> alter session set container=PDB1;
SQL> grant select on sys.v_$parameter to scott;

init.ora

control_files='/opt/oracle/oradata/CDB/control01.ctl' 
db_block_size=8192 
db_name='CDB' In

The parameter file is used at the very least to get the name of the database and the location of the control files. The control files tell Oracle the location of every other file, so they are very important to the “bootstrap” process that starts the instance.

The naming convention for this file by default is

  • init$ORACLE_SID.ora (UNIX/Linux environment variable)
  • init%ORACLE_SID%.ora (Windows environment variable)

Location

  • $ORACLE_HOME/dbs (UNIX/Linux)
  • %ORACLE_HOME%\DATABASE (Windows)

Server Parameter Files (SPFILEs)

  • SPFILE is always stored on the database server; the SPFILE must exist on the server machine itself and can’t be located on the client machine.

  • The ALTER SYSTEM command lets you write values directly into the SPFILE. Administrators no longer have to find and maintain all of the parameter files by hand.

The naming convention for this file by default is

  • $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora (UNIX/Linux environment variable) * * * *%ORACLE_HOME/database/spfile%ORACLE_SID%.ora (Windows environment variable)
Alter system set parameter=value <comment='text'> <deferred>  <scope=memory|spfile|both> <sid='sid|*'>  <container=current|all>
  • deferred specifies whether the system change takes place for subsequent sessions only (not currently established sessions, including the one making the change). By default, the ALTER SYSTEM command will take effect immediately, but some parameters can’t be changed immediately—they can be changed only for newly established sessions. We can use the following query to see what parameters mandate the use of deferred:
SQL> select name from v$parameter where issys_modifiable='DEFERRED';

SQL> alter system set sort_area_size = 65536;
alter system set sort_area_size = 65536  * ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option SQL> alter system set sort_area_size = 65536 deferred;
System altered.

SCOPE=MEMORY, SCOPE=BOTH, and SCOPE=SPFILE clauses of the ALTER SYSTEM SQL statement, respectively, when you use the ALTER SYSTEM statement to change initialization parameters.

Utrata lub uszkodzenie pliku SPFILE może prowadzić do poważnych problemów. Jedną z opcji jest użycie kopii zapasowej RMAN, jeśli włączono funkcję automatycznego tworzenia kopii zapasowych. Dopóki baza danych jest uruchomiona, można utworzyć plik SPFILE na podstawie bieżących parametrów uruchomionej instancji. Parametry można uczynić czytelnymi poprzez ich późniejszą konwersję do pliku parametrów init. Należy pamiętać, że zmiany parametrów, które mają miejsce wyłącznie w pamięci, nie odzwierciedlają wartości oryginalnego pliku SPFILE.

CREATE SPFILE='/tmp/[email protected]' FROM MEMORY;
CREATE PFILE='/tmp/[email protected]' FROM SPFILE='/tmp/[email protected]';

Setting Values in SPFILEs

The ALTER SYSTEM SET command, by default, will update the currently running instance and make the change to the SPFILE for You.

  • deferred specifies whether the system change takes place for subsequent sessions only (not currently established sessions, including the one making the change). By default, the ALTER SYSTEM command will take effect immediately, but some parameters can’t be changed immediately—they can be changed only for newly established sessions.
select name from v$parameter where issys_modifiable='DEFERRED';

SQL> alter system set sort_area_size = 65536;
alter system set sort_area_size = 65536  * ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option 
SQL> alter system set sort_area_size = 65536 deferred;
System altered
  • SCOPE=MEMORY|SPFILE|BOTH indicates the “scope” of this parameter setting. Here are our choices for setting the parameter value:
  • SCOPE=MEMORY changes the setting in the instance(s) only; it will not survive a database restart. The next time you start the database, the setting will be whatever was already recorded in the SPFILE.
  • SCOPE=SPFILE changes the value in the SPFILE only. The change will not take place until the database is restarted and the SPFILE is processed again. Some parameters can be changed only by using this option. For example, the processes parameter must use SCOPE=SPFILE, as you can’t change the active instance value.
  • SCOPE=BOTH means the parameter change takes place both in memory and in the SPFILE. The change will be reflected in the current instance, and, the next time you start, this change will still be in effect. This is the default value for scope when using an SPFILE. With an init.ora parameter file, the default and only valid value is SCOPE=MEMORY. This is the default if the instance was started with an SPFILE.

Setting value and comment

SQL> alter system set pga_aggregate_target=512m comment = 'AWR recommendation';
System altered.
SQL> select value, update_comment from v$parameter where name = 'pga_ aggregate_target';
⚠️ **GitHub.com Fallback** ⚠️