Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Saturday, September 12, 2015

Create and manage database configuration files

Create and manage database configuration files

Database configuration files that are needed are those stored under the respective $ORACLE_HOME/dbs directory:

  1. The relevant files are, for example, the pfile/spfile and the passwordfile.
  2. Other files that may have to be studied could be /etc/oratab, the Oracle Net configuration files in $ORACLE_HOME/network/admin


[oracle@collabn1 ~]$ cd $ORACLE_HOME/dbs

[oracle@collabn1 dbs]$ ls -lrt

total 9632
-rw-r--r-- 1 oracle oinstall    2851 May 15  2009    init.ora
-rw-r----- 1 oracle oinstall    1536 Aug  1 18:18     orapwSHAIKPRD1
-rw-r--r-- 1 oracle oinstall      66 Aug  3 13:16           initSHAIKPRD1.ora



Views available to determine the parameters:

SHAIKDB>select table_name from dict where table_name like '%PARAMETER%';

TABLE_NAME
------------------------------
ALL_APPLY_PARAMETERS
ALL_CAPTURE_PARAMETERS
DBA_ADVISOR_DEF_PARAMETERS
DBA_ADVISOR_EXEC_PARAMETERS
DBA_ADVISOR_PARAMETERS
DBA_ADVISOR_PARAMETERS_PROJ
DBA_ADVISOR_SQLW_PARAMETERS
DBA_APPLY_PARAMETERS
USER_ADVISOR_EXEC_PARAMETERS
USER_ADVISOR_PARAMETERS
USER_ADVISOR_SQLW_PARAMETERS
DBA_REPPARAMETER_COLUMN
USER_REPPARAMETER_COLUMN
ALL_REPPARAMETER_COLUMN
DBA_HIST_PARAMETER
DBA_HIST_PARAMETER_NAME
DBA_LOGSTDBY_PARAMETERS
DBA_CAPTURE_PARAMETERS
NLS_DATABASE_PARAMETERS
NLS_INSTANCE_PARAMETERS
NLS_SESSION_PARAMETERS
V$NLS_PARAMETERS
V$OBSOLETE_PARAMETER
V$PARAMETER
V$PARAMETER2
V$PARAMETER_VALID_VALUES
V$SPPARAMETER
V$SYSTEM_PARAMETER
V$SYSTEM_PARAMETER2
GV$HS_PARAMETER
GV$SPPARAMETER
V$HS_PARAMETER
V$LOGMNR_PARAMETERS
GV$LOGMNR_PARAMETERS
GV$NLS_PARAMETERS
GV$OBSOLETE_PARAMETER
GV$PARAMETER
GV$PARAMETER2
GV$PARAMETER_VALID_VALUES
GV$SYSTEM_PARAMETER
GV$SYSTEM_PARAMETER2

41 rows selected.



SHOW PARAMETERS
This SQL*Plus command displays the values of initialization parameters in effect for the current session.
SHOW SPPARAMETERS
This SQL*Plus command displays the values of initialization parameters in the server parameter file (SPFILE).
CREATE PFILE
This SQL statement creates a text initialization parameter file (PFILE) from the SPFILE or from the current in-memory settings. You can then view the PFILE with any text editor.
V$PARAMETER
This view displays the values of initialization parameters in effect for the current session.
V$PARAMETER2
This view displays the values of initialization parameters in effect for the current session. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.
V$SYSTEM_PARAMETER
This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values.
V$SYSTEM_PARAMETER2
This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.
V$SPPARAMETER
This view displays the current contents of the SPFILE. The view returns FALSE values in the ISSPECIFIED column if an SPFILE is not being used by the instance.


SHAIKDB>desc v$parameter
Name                              Null?    Type
----------------------------------------------------- -------- ------------------------------------
NUM                               NUMBER
NAME                               VARCHAR2(80)
TYPE                               NUMBER
VALUE                               VARCHAR2(4000)
DISPLAY_VALUE                           VARCHAR2(4000)
ISDEFAULT                           VARCHAR2(9)
ISSES_MODIFIABLE                       VARCHAR2(5)
ISSYS_MODIFIABLE                       VARCHAR2(9)
ISINSTANCE_MODIFIABLE                       VARCHAR2(5)
ISMODIFIED                           VARCHAR2(10)
ISADJUSTED                           VARCHAR2(5)
ISDEPRECATED                           VARCHAR2(5)
ISBASIC                           VARCHAR2(5)
DESCRIPTION                           VARCHAR2(255)
UPDATE_COMMENT                        VARCHAR2(255)
HASH                               NUMBER

SHAIKDB>desc v$parameter2
Name                              Null?    Type
----------------------------------------------------- -------- ------------------------------------
NUM                               NUMBER
NAME                               VARCHAR2(80)
TYPE                               NUMBER
VALUE                               VARCHAR2(4000)
DISPLAY_VALUE                           VARCHAR2(4000)
ISDEFAULT                           VARCHAR2(6)
ISSES_MODIFIABLE                       VARCHAR2(5)
ISSYS_MODIFIABLE                       VARCHAR2(9)
ISINSTANCE_MODIFIABLE                       VARCHAR2(5)
ISMODIFIED                           VARCHAR2(10)
ISADJUSTED                           VARCHAR2(5)
ISDEPRECATED                           VARCHAR2(5)
ISBASIC                           VARCHAR2(5)
DESCRIPTION                           VARCHAR2(255)
ORDINAL                           NUMBER
UPDATE_COMMENT                        VARCHAR2(255)


SHAIKDB>col value for a40
SHAIKDB>col name for a40
SHAIKDB>select name,value from v$parameter2 where upper(name) like '%PFILE%';



NAME                  VALUE
------------------------------ ----------------------------------------
spfile                  /u01/app/oracle/product/11.2.0.2/SHAIKPR
                 OD/dbs/spfileMYDB.ora


SHAIKDB>create pfile from spfile;

File created.

SHAIKDB>!ls -lrt $ORACLE_HOME/dbs/*MYDB*.ora
-rw-r----- 1 oracle dba 3584 Sep 10 21:25 /u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/spfileMYDB.ora
-rw-r--r-- 1 oracle dba  977 Sep 12 12:37 /u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/initMYDB.ora

SHAIKDB>select sysdate from dual;

SYSDATE
---------
12-SEP-15


SHAIKDB>create pfile='/home/oracle/sshaik/mydb.ora' from spfile;

File created.

SHAIKDB>!ls -lrt /home/oracle/sshaik/mydb.ora
-rw-r--r-- 1 oracle dba 977 Sep 12 12:38 /home/oracle/sshaik/mydb.ora

SHAIKDB>



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

SHAIKDB>startup pfile='/home/oracle/sshaik/mydb.ora';
ORACLE instance started.

Total System Global Area  208769024 bytes
Fixed Size           2211928 bytes
Variable Size         159387560 bytes
Database Buffers       41943040 bytes
Redo Buffers           5226496 bytes
Database mounted.
Database opened.
SHAIKDB>select name,value from v$parameter2 where upper(name) like '%PFILE%';

NAME                  VALUE
------------------------------ ----------------------------------------
spfile

SHAIKDB>create spfile from pfile;

File created.

SHAIKDB>!ls -lrt $ORACLE_HOME/dbs/*MYDB*.ora
-rw-r--r-- 1 oracle dba  977 Sep 12 12:37 /u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/initMYDB.ora
-rw-r----- 1 oracle dba 3584 Sep 12 12:39 /u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/spfileMYDB.ora

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


SHAIKDB>startup
ORACLE instance started.

Total System Global Area  208769024 bytes
Fixed Size           2211928 bytes
Variable Size         159387560 bytes
Database Buffers       41943040 bytes
Redo Buffers           5226496 bytes
Database mounted.
Database opened.

SHAIKDB>select name,value from v$parameter2 where upper(name) like '%PFILE%';

NAME                  VALUE
------------------------------ ----------------------------------------
spfile                  /u01/app/oracle/product/11.2.0.2/SHAIKPR
                 OD/dbs/spfileMYDB.ora


If the instance is running, issue the following command to re-create the SPFILE from the current values of initialization parameters in memory:

SHAIKDB>CREATE SPFILE FROM MEMORY;
Changing parameter values:


SCOPE = SPFILE
The change is applied in the server parameter file only. The effect is as follows: No change is made to the current instance. For both dynamic and static parameters, the change is effective at the next startup and is persistent. This is the only SCOPE specification allowed for static parameters.
SCOPE = MEMORY
The change is applied in memory only. The effect is as follows: The change is made to the current instance and is effective immediately. For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated. For static parameters, this specification is not allowed.
SCOPE = BOTH
The change is applied in both the server parameter file and memory. The effect is as follows: The change is made to the current instance and is effective immediately. For dynamic parameters, the effect is persistent because the server parameter file is updated. For static parameters, this specification is not allowed.


Clearing Initialization Parameter Values

You can use the ALTER SYSTEM RESET command to clear (remove) the setting of any initialization parameter in the SPFILE that was used to start the instance. Neither SCOPE=MEMORY nor SCOPE=BOTH are allowed. The SCOPE = SPFILE clause is not required, but can be included.

[oracle@collabn1 dbs]$ orapwd --help
Usage: orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>

 where
    file - name of password file (required),
    password - password for SYS will be prompted if not specified at command line,
    entries - maximum number of distinct DBA (optional),
    force - whether to overwrite existing file (optional),
    ignorecase - passwords are case-insensitive (optional),
    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).
   
 There must be no spaces around the equal-to (=) character.

[oracle@collabn1 dbs]$ orapwd file=orapwMYDB password=test123 entries=5 ignorecase=yes

[oracle@collabn1 dbs]$ ls -lrt orapwMYDB
-rw-r----- 1 oracle oinstall 2048 Sep 12 13:58 orapwMYDB

No comments: