 |
|
Oracle Tips by Burleson |
Non-Dynamic Oracle
Parameters
One of the hallmarks of a competent Oracle
administrator is their ability to quickly ascertain the configuration
of any Oracle database. As we know from the reading assignment,
Oracle is the world’s most powerful and flexible databases, and there
are over 250 configuration parameters.
All of these parameters are necessary to allow
you an almost infinite choice of configurations, from data warehouse
to OLTP. To be effective with Oracle, it is your challenge to learn
to quickly display and evaluate the Oracle configuration parameters.
Starting with Oracle 10g, all Oracle
parameters can be changed dynamically with “alter system” commands.
Prior to Oracle 10g, Oracle parameters were kept in a flat file on the
operating system called init$ORACLE_SID.ora. This init.ora file
contained all initialization parameters, and the Oracle8i
administrator would have to “bounce” (stop and re-start the instance)
to make changes.
Let’s begin with a review of the location of
the Oracle configuration parameters.
There are some parameters that can only be
changed by bouncing the Oracle database and issuing special “alter
database” commands. These parameters are specified at Oracle database
startup time. To see how this works, let’s take a look at the syntax
used to create an Oracle database
Here is an example of an actual database
create command for Oracle.
create
database
"diogenes"
maxinstances 1
maxlogfiles 16
maxloghistory 226
maxlogmembers 2
maxdatafiles 30
noarchivelog
character set "US7ASCII"
SET TIME_ZONE = 'PST';
datafile
'c:\oracle\oradata\system01.dbf' size 246M
logfile
group 1 'c:\oracle\oradata\log01.dbf' size 50K,
group 2 'c:\oracle\oradata\log02.dbf' size 50K,
group 3 'c:\oracle\oradata\log03.dbf' size 50K
;
All of these parameters are set at Oracle database creation time, and
some can be quite difficult to change. Hence, it is critical to get
these parameters correct before the database is created. Let’s look at
a few examples and see how this works.
Changing ARCHIVELOG mode
As an example, let’s look at the NOARCHIVELOG
parameter. If you want to change your database to start archiving
your redo logs, you must stop and re-start the database.
SQL> connect
internal as sysdba;
SQL>
shutdown immediate
SQL> startup
mount;
SQL> ALTER
DATABASE ARCHIVELOG;
SQL> alter
database open;
SQL> alter
system set log_archive_start=true;
One the database is opened in ARCHIVELOG mode,
the database will start using the ARCH background process to copy the
redo logs from the online redo logs to the archived redo log
filesystem on your server.
Changing db_block_size
The Oracle database block size is used during
the creation of all Oracle data files. Prior to the advent of
multiple blocksizes in Oracle 10g, the Oracle DBA would have to take
the system offline for many hours and perform the following steps:
1 - Export the Oracle database
2 - Drop the Oracle database
3 – Re-create the Oracle database with a
larger block size
4 - Re-import the data into the new Oracle
database
However, since Oracle 10g was introduced, it
becomes easy to create new tablespaces with different blocksizes and
move data into the new blocksizes. For example, here we create a 32k
tablespace file and move a table into the new tablespace:
create
tablespace
32k_tablespace
datafile
'/u01/oradata/mysid/32k_file.dbf'
size
100M
blocksize
32k
;
alter table
customer move tablespace 32k_tablespace;
Changing the character set
The Oracle database supports a huge number of
character sets from Kanji to Hebrew. Once defined, it is quite
difficult to change without complete reorganizing the whole database.
However, some character sets are legitimate sub-sets of “supersets”
and they can be changed.
Attempting to change the Oracle database
character set to a character set that is not a strict superset can
result in data loss and data corruption. To ensure data integrity,
whenever migrating to a new character set that is not a strict
superset, you must use export/import.
It is essential to do a full backup of the
Oracle database before using the ALTER DATABASE [NATIONAL] CHARACTER
SET statement, since the command cannot be rolled back. Here is an
example of a Oracle database character set change:
SQL> startup
mount;
SQL> alter system enable restriced session;
SQL> alter system set job_queue_processes=0;
SQL> alter database open;
SQL> alter database character set;
SQL> shutdown immediate;
SQL> startup;
Next, let’s take a look dynamic parameters.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
