Tuesday, January 20, 2009

Altering commit behaviour 10gR2 and up

In Oracle 10g Release 2 the COMMIT command has been enhanced with the WRITE clause to give a degree of control over the way redo information is written to the redo logs during the commit operation.
This can improve performance, but it should only be used for processes that meet the following criteria:
1) They result in large numbers of transactions that require redo log writes.
2) Data loss can be tolerated in the event of an instance crash during the process.
3) Waiting for redo log writes is a significant part of the waits associated with the process.

The available options for the COMMIT command and the WRITE clause are displayed below.
COMMIT;
COMMIT WRITE WAIT;
COMMIT WRITE NOWAIT;
COMMIT WRITE BATCH;
COMMIT WRITE IMMEDIATE;

The meanings of the WRITE clause values are listed below.
IMMEDIATE - The commit "prods" the LGWR process by sending a message, so that the redo is written imemdiately to the redo logs.
BATCH - The writes to the redo logs are buffered.
WAIT - The commit command is synchronous. It doesn't return until the relevant redo information is written to the online redo log.
NOWAIT - The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log.

The action associated with the regular COMMIT command is defined by the COMMIT_WRITE parameter, which accepts a comma-separated list of values.
COMMIT_WRITE = '{IMMEDIATE BATCH},{WAIT NOWAIT}'

The COMMIT_WRITE parameter can be specified at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.

ALTER [SYSTEM SESSION] SET COMMIT_WRITE='WAIT';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='NOWAIT';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='IMMEDIATE';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='BATCH';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='BATCH,WAIT';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='BATCH,NOWAIT';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='IMMEDIATE,WAIT';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='IMMEDIATE,NOWAIT';

The default actions for the COMMIT_WRITE parameter and WRITE clause are the same, although at the time of writing the COMMIT_WRITE documentation incorrectly says they are not, so refer to the COMMIT documentations, which says:
"If you specify neither WAIT nor NOWAIT, then WAIT is the default. If you specify neither IMMEDIATE nor BATCH, then IMMEDIATE is the default."

Automate schema statistics gathering on 9i

First enable monitoring on your schema
exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING('SCHEMA',TRUE);

Then for first time only take statistics on all objects.
exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA',estimate_percent => 5,cascade => TRUE);

After first time you can use the gather stale option to take statistics only for stale objects.

exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA',estimate_percent => 5,cascade => TRUE,options => 'GATHER STALE');

Changing ORACLE_SID, database name

1) Find instance name and db name
SQL> select instance from v$thread; --> Instance name
SQL> select name from v$database; --> Database name

2) Change instance name

SQL> create pfile from spfile;
SQL> shutdown immediate;

Change ORACLE_SID on /etc/oratab and .profile scripts, tnsnames.ora

Go to the $ORACLE_HOME/dbs directory and change the name of the pfile , review pfile entries. Set ORACLE_SID variable pointing the new instance name and create a new password file.
SQL>create spfile from pfile;
SQL>startup;

Run the following sql to verify the instance name changed

SQL> select instance from v$thread;
SQL> select name from v$database;

3) Change database name

Attention the db_name must be 8 characters.

Connect as sysdba and do
SQL> alter system switch logfile; to force a checkpoint
SQL> alter database backup controlfile to trace resetlogs;
SQL> shutdown immediate;

Edit the trace file contaning the control file backup by copying all commands from startup nomount to the end. Change the REUSE DATABASE "OLD_DB_NAME" to SET DATABASE "NEW_DBNAME".
Recovery commands will ne needed if the shutdown was not normal or immediate; Copy the commands until ALTER DATABASE OPEN RESETLOGS;

Edit in pfile the db_name parameter. Take a backup of previous control files if you did not change the control_files parameter in the pfile.

SQL> create spfile from pfile;
SQL> @cf.sql (control file script)

You should see ..

Control file created.
Database altered.

SQL> shutdown immediate;
SQL> startup
SQL> create pfile from spfile;
SQL> select instance from v$thread;
SQL> select name from v$database;

To confirm changes !!!

Add tempfile to temporary tablespace if needed.

If you have configured dbconsole you must drop and recreate repository.

Changing archivelog mode

shutdown the database , mount the database and issue.

alter database archivelog; --> Enable Archiving
alter database noarchivelog; --> Disable Archiving
alter database open;

archive log list in order to se the archive mode.

select_catalog_role

If The select_catalog_role is granted to a user the user can view the dynamic views.

Wednesday, January 14, 2009

TNS-01201

Problem Description:
====================
Using SQL*Net v2.0 on Unix, you are starting the tnslsnr using the syntax:
% lsnrctl start
You get: TNS-01201: Listener cannot find executable //bin/oracle for SID [SID] where is an actual operating system pathname.

Solution Description:
====================
Where does the file listener.ora reside?
- If it is not in /etc (or /var/opt/oracle), $ORACLE_HOME/network/admin or $TNS_ADMIN, set TNS_ADMIN to point to the correct directory or move the files to the appropriate place.

If the listener.ora is in the correct location, what is the actual path of $ORACLE_HOME, and the path specified by ORACLE_HOME in the listener.ora?
- Most likely these two values will not match; ORACLE_HOME in the listener.ora file must be set to the correct value.
In the listener.ora file, under the description for SID_DESC, there is the parameter ORACLE_HOME= as follows:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=)
(ORACLE_HOME=) <<<< This is the entry ) )

The error TNS-01201 indicates that the specified for ORACLE_HOME is incorrect.
It may indicate typographical errors, incorrect case, or missing directories in the path to ORACLE_HOME.

Using the 10G Shrink Command

Oracle10G objects that reside in Automatic Segment Space Managed tablespaces can be shrunk using the "ALTER………SHRINK" statement. The shrink statement is not limited to just heap tables and indexes, Oracle also allows index-organized tables, partitions, subpartitions, materialized views and materialized view logs to be shrunk using ALTER commands. In addition, the CASCADE option can be used to propagate the shrink operation to all dependent objects except materialized views, LOB indexes, IOT mapping tables and overflow segments.

It is interesting to note that because the shrink operation may change the ROWIDS in heap-organized tables, row movement must first be enabled by executing the following command:

ALTER TABLE < tablename> ENABLE ROW MOVEMENT;

The shrink operation can be performed while the table is on-line and does not require any extra database data file space. The shrink operation itself is performed by the database internally executing INSERT and DELETE statements. Since the data itself is not changed (just rearranged), DML triggers are not fired during shrink operations.

The shrink operation will free unused space both above and below the high water mark. Rows are moved from one block to another which is why row movement must be enabled on the table beforehand. Unlike the ALTER TABLE....MOVE statement, indexes do not need to be rebuilt after the shrink operation is executed.

alter table ... shrink is subject to the following restrictions:

· You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
· Segment shrink is not supported for tables with function-based indexes or bitmap join indexes. · This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
· You cannot specify this clause for a compressed table.
· You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.
· Segment shrink is not supported for tables with Domain indexes.