Nethence Documentation Lab Webmail Your IP BBDock  


Those documents are obsolete, please use the Nethence Documentation instead.

HomeUnixWindowsOracleObsoleteHardwareDIYMechanicsScriptsConfigsPrivate

Oracle Database tuning
 
 
You may proceed with the further examples below for some little tuning. For that purpore, you might want to prepare some additional directory,
su -
cd /usr/local
mkdir -p oracle/dbname
chown oracle:oinstall oracle
chown oracle:oinstall oracle/dbname
chmod 750 oracle
chmod 750 oracle/dbname
 
 
Control files
Back to oracle user, connect to your database (at least started nomount) and edit the spfile,
select name from v$controlfile;
alter system
set control_files='/u02/oradata/dbname/control01.ctl',
'/u02/flash_recovery_area/dbname/control02.ctl',
'/usr/local/oracle/dbname/control03.ctl'
scope=spfile;
shutdown immediate
note. on 10g, three control files by default, move the relevant ones e.g.,
cd /u02/oradata/dbname
mv control02.ctl ../../flash_recovery_area/dbname/
mv control03.ctl /usr/local/oracle/dbname/
note. only two control files by default on 11gR2, hence after shutdown, copy the control file instead of moving it (control files are identical, they're just backups of one another),
cp /u02/oradata/dbname/control01.ctl /usr/local/dbname/control03.ctl
check the spfile,
cd $ORACLE_HOME/dbs
grep -a control_files spfiledbname.ora
note. it's a binary file, don't bother if control file paths and filenames are crippeled somehow
start the database (using modified spfile) and check,
startup
select name from v$controlfile;
 
 
Redo logs
Rename to .rdo
Review the current names and shutdown the database,
set linesize 140
column member format a40;
select * from v$logfile;
shutdown immediate
 
Rename the redo log files,
cd /u02/oradata/dbname
mv redo01.log redo01a.rdo
mv redo02.log redo02a.rdo
mv redo03.log redo03a.rdo
 
Start the database (mount) and apply the change,
startup mount
alter database rename file '/u02/oradata/dbname/redo01.log' to '/u02/oradata/dbname/redo01a.rdo';
alter database rename file '/u02/oradata/dbname/redo02.log' to '/u02/oradata/dbname/redo02a.rdo';
alter database rename file '/u02/oradata/dbname/redo03.log' to '/u02/oradata/dbname/redo03a.rdo';
alter database open;
 
Add new members
Make sure the redo log file 1 is CURRENT and the others INACTIVE,
--alter system switch logfile;
--alter system flush buffer_cache;
select * from v$log;
 
Add a new member to each redo log group,
alter database add logfile member '/usr/local/oracle/dbname/redo01b.rdo' to group 1;
alter database add logfile member '/usr/local/oracle/dbname/redo02b.rdo' to group 2;
alter database add logfile member '/usr/local/oracle/dbname/redo03b.rdo' to group 3;
the new members are currently INVALID,
set linesize 140
column member format a40;
  select * from v$logfile;
force the update and check again,
alter system switch logfile;
/
/
  select * from v$logfile;
 
Note. if you remove a redo file, it still continues to write. Even a database shutdown and startup won't make any troubbles. You'll have to check the alert log and v$logfile manually.
 

Last update: Jun 26, 2010