Database Reference
In-Depth Information
SPFILE will contain the following entries for the pga_aggregate_target parameter after executing the preceding
two commands.
*.pga_aggregate_target=5120M
RAC1.pga_aggregate_target=10240M
The command to reset or remove a parameter from spfile should match with the scope of the parameter if the
parameter exists in spfile already. For example, to remove *.pga_aggregate_target from spfile completely, you must
specify the sid clause matching the spfile scope.
alter system reset pga_aggregate_target scope=both sid='*';
Not all parameters can be modified at the instance level. Column ISINSTANCE_MODIFIABLE in v$parameter
shows whether a parameter can be modified at the instance level or not. The following output of SQL querying
v$parameter shows that pga_aggregate_target parameter can be modified at the instance level.
SQL> select name, ISINSTANCE_MODIFIABLE from v$parameter
where name='pga_aggregate_target'
NAME ISINS
------------------------------ -----
pga_aggregate_target TRUE
Password File Management
Password files allow non-sys users to log in to the database with elevated privileges such as sysdba, sysoper, sysbackup
(12c), sysdg (12c), etc. In 11gR2, each database instance is considered to have a non-shared password file, and so
grants in an instance are not propagated to other database instances. You must copy password files to each node and
rerun grants connecting to local instance in version 11.2.
In version 11.2 and earlier, it is a better approach to store the password files in a shared file system such as NFS or
cluster file system. With this shared password file approach, you can maintain the password files with ease, but grants
still need to be executed in every node.
$ orapwd file='orapworcl12' entries=10
In version 12c, password files can be stored in ASM, and this greatly simplifies password file management.
By default, password files are stored in the ASM disk group.
+DATA/ORCL12/PASSWORD/pwdorcl12.268.804626815
The following command shows how a password file can be created in Oracle ASM disk group. As the password
file is stored in ASM, all instances can access the password file.
$ orapwd file='+DATA/orcl12/orapworcl12' entries=8 dbuniquename='orcl12' format=12
Prior to version 11.2, you must grant privileges in every instance explicitly, as the sysdba grants operate at the
instance level. This behavior is due to an inherent assumption that the password file is not shared between the
instances. For example, in the following output, granting sysdba to rs user connecting to RAC2 instance populated the
password file only for instance 2.
SQL> grant sysdba to rs;
Grant succeeded.
