Oracle 11g – Database Instance Administration
In this post, we will walk through some of the important scripts which we use in our day-to-day database instance administration.we will learn about spfile and pfile locations and how to create them in detail.
How to create spfile from pfile
SYS> create spfile from pfile;
File created.
SYS>
How to create pfile from spfile
SYS> create pfile from spfile;
File created.
The default location is $ORACLE_HOME/dbs folder where the pfile and spfile is created.
How to check database is running using spfile or pfile
SYS> show parameter spfile;
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/product/11.2.0
/db_2/dbs/spfileTEST.ora
SYS>
if the output shows spfile, the database is running with spfile.
Some of the important dictionary views for managing an Oracle database instance are
show parameters
show spparameters
v$spparameter
v$parameter
v$system_parameter
Opening the database in read-only mode
SYS> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> startup mount;
ORACLE instance started.
Total System Global Area 6246658048 bytes
Fixed Size 2239936 bytes
Variable Size 3254780480 bytes
Database Buffers 2969567232 bytes
Redo Buffers 20070400 bytes
Database mounted.
SYS> alter database open read only;
Database altered.
How to check oracle log file location :
v$diag_info
or
SYS> show parameter dump
NAME TYPE VALUE
———————————— ———– ——————————
background_core_dump string partial
background_dump_dest string /u01/app/diag/rdbms/test/TEST/
trace
core_dump_dest string /u01/app/diag/rdbms/test/TEST/
cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /u01/app/diag/rdbms/test/TEST/
trace
Here, the alert log file is found in /u01/app/diag/rdbms/test/TEST/trace folder.
How to check SGA allocation
The SHOW SGA SQL command will show you the SGA memory allocations.
In order to execute SHOW SGA you must be connected with the special privilege SYSDBA (which is only available to user accounts that are members of the DBA Linux group).
SQL> connect / as sysdba
Connected.
SQL> show sga
Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 1006633528 bytes
Database Buffers 587202560 bytes
Redo Buffers 14692352 bytes
Changing Parameter Values
You change the value of a parameter by editing the initialization parameter file. In most cases, the new value takes effect the next time you start an instance of the database. However, you can change the value of some parameters for the duration of the current session, as described in the following section.
There are two types of parameter.
DYNAMIC – The change in the parameter will be effected immediately
STATIC – Needs instance restart
how to identify a static or dynamic parameter in Oracle
SYS> select NAME, ISSYS_MODIFIABLE from v$parameter where NAME in(‘undo_retention’,’db_writer_processes’);
NAME ISSYS_MOD
————————- ———
db_writer_processes FALSE
undo_retention IMMEDIATE
IMMEDIATE or TRUE => DYNAMIC
FALSE => STATIC (NEED RESTART TO TAKE EFFECT)
e.g.,
SYS>
SYS> show parameter db_writer_processes;
NAME TYPE VALUE
———————————— ———– ——————————
db_writer_processes integer 1
SYS> show parameter undo_retention;
NAME TYPE VALUE
———————————— ———– ——————————
undo_retention integer 900
SYS>
SYS> alter system set undo_retention = 1000;
System altered.
SYS> show parameter undo_retention;
NAME TYPE VALUE
———————————— ———– ——————————
undo_retention integer 1000
SYS> show parameter db_writer_processes
Here undo_retention changed immediately
NAME TYPE VALUE
———————————— ———– ——————————
db_writer_processes integer 1
SYS> alter system set db_writer_processes=2;
alter system set db_writer_processes=2
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SYS> alter system set db_writer_processes=2 scope=spfile;
System altered.
SYS> show parameter db_writer_processes
NAME TYPE VALUE
———————————— ———– ——————————
db_writer_processes integer 1
Here it has not changed and the solution is to restart the instance.
SYS> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> startup;
ORACLE instance started.
Total System Global Area 6246658048 bytes
Fixed Size 2239936 bytes
Variable Size 3254780480 bytes
Database Buffers 2969567232 bytes
Redo Buffers 20070400 bytes
Database mounted.
Database opened.
SYS> show parameter db_writer_processes;
NAME TYPE VALUE
———————————— ———– ——————————
db_writer_processes integer 2
SYS> !ps -ef | grep dbw
oracle 13465 1 0 18:28 ? 00:00:00 ora_dbw0_TEST
oracle 13467 1 0 18:28 ? 00:00:00 ora_dbw1_TEST
oracle 13696 13100 0 18:29 pts/1 00:00:00 /bin/bash -c ps -ef | grep dbw
oracle 13698 13696 0 18:29 pts/1 00:00:00 grep dbw
Words from dbapath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.