Wednesday, December 23, 2009

Setting db_file_multiblock_read_count

A very nice procedure to see the effects on a full table scan query and to estimate the value for db_file_multiblock_read_count.

DECLARE
l_count PLS_INTEGER;
l_time PLS_INTEGER;
l_starting_time PLS_INTEGER;
l_ending_time PLS_INTEGER;
BEGIN
dbms_output.put_line('dbfmbrc seconds');
FOR l_dbfmbrc IN 1..32
LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count='||l_dbfmbrc;
l_starting_time := dbms_utility.get_time();
SELECT /*+ full(t) */ count(*) INTO l_count FROM big_table t;
l_ending_time := dbms_utility.get_time();
l_time := round((l_ending_time-l_starting_time)/100);
dbms_output.put_line(l_dbfmbrc||' '||l_time);
END LOOP;
END;
/

Table must be enough big in order not to be cached !!!

Taken by an excellent book (Apress:Troubleshooting Oracle Performance)

Monday, December 7, 2009

ORA-19588

RMAN backup of archivelogs fails:

ORA-19588: archived log recid xxxx stamp xxxxxxxx is no longer valid

The rman backup job had been started TWICE.

The initial resync for each job identified the same set of logs to be backed up.
Both jobs were trying to backup and delete the SAME archivelog set, one of which failed eventually as the other job had already backed up and deleted the log.


Make sure that concurrent backups of the archivelogs are not run.

Friday, December 4, 2009

CTAS and column default values

Affecting 8i up to 10gR2

If you use CTAS ( Create Table As Select ) to copy a table, the new duplicate table does not contain the default values of the original table. As a result, if you use DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS, it does not copy column's default values.


To implement the solution, please execute the following steps:

1) Do not use CTAS to copy the table, use another method.

2) Use CTAS and then modify columns giving them their default values where necessary.

3) If you want the column's default values to be copied when using DBMS_REDEFINITION, the table has to be pre-created with the default column values before getting re-organized with DBMS_REDEFINITION.

Manually purge scheduler jobs logs

From sqlplus as sys or system run :

exec DBMS_SCHEDULER.PURGE_LOG();

ORA-00959: tablespace '_$deleted$0$0' does not exist

If this error occurs when you try to add a partition on partition table then all the table partitions had once been moved to another tablespace and the original tablespace has now been dropped or new tablespace where partitons now reside has been renamed to the original tablespace name.

Issue

alter table [table_name] modify default attributes tablespace [tablespace name];

Wednesday, December 2, 2009

Oracle Security Options

A nice FAQ for Oracle Security

http://www.orafaq.com/wiki/Oracle_database_Security_FAQ

Oracle 10g : View bind variables values without tracing

In 10g there is a new view called: V$SQL_BIND_CAPTURE

V$SQL_BIND_CAPTURE displays information on bind variables used by SQL cursors.Each row in the view contains information for one bind variable defined in a cursor such as :

1) Reference to the cursor defining the bind variable
(hash_value, address) for the parent cursor and (hash_value, child_address) for the child cursor.
2) Bind metadata
Name, position, datatype, character set ID, precision, scale, and maximum length of the bind variable.

3) Bind data
One of the bind values used for the bind variable during a past execution of its associated SQL statement.

Bind values are not always captured for this view. Bind values are displayed by this view only when the type of the bind variable is simple (this excludes LONG, LOB, and ADT datatypes) and when the bind variable is used in the WHERE or HAVING clauses of the SQL statement( This excludes update and insert statements).

You can query this view using sql_id or hash_value for an sql statement.

e.g

1) Find has_value for your statement

select sql_text,sql_id,hash_value from v$sqlarea
where lower(sql_text) like '%[my statement]%';

2) FInd bind variables values

SELECT name,datatype_string,was_captured,last_captured,value_string,value_anydata
FROM v$sql_bind_capture
WHERE hash_value='[hash_value_from_previous_sql]';

Friday, November 27, 2009

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

As Oracle documentation states that "none of the columns in the unique/primary key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the unique/primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE".

So if you have a table that has a TIMESTAMP WITH TIME ZONE column this column cant have or be part of a unique/primary key constraint. In order to bypass this you can create a unique index including this column.

e.g

create table test (a int primary key,b int, c timestamp with time zone);

alter table test add constraint test_u01 unique (b,c);

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

create unique index test_u01 on test(b,c);

Index created !!!

Thursday, November 26, 2009

Date and time arithmetics with interval

-- Add five years to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' year FROM dual;

-- Add five months to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' month
FROM dual;

-- Add five days to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' DAY
FROM dual;

-- Add five hours to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' hour
FROM dual;

-- Add five hours and 10 minutes to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5:10' hour to minute
FROM dual;

-- Add 10 minutes to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '10' MINUTE
FROM dual;

-- Add 10 minutes and 30 seconds to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '10:30' MINUTE TO SECOND
FROM dual;

-- Add 30 seconds to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '30' SECOND
FROM dual;

Wednesday, November 25, 2009

Oracle 10g flashback table feature

FLASHBACK TABLE enables to recover a table to a point in time in the past without restoring a backup.The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system.You cannot restore a table to an earlier state across any DDL operations that change the structure of the table.You cannot roll back a FLASHBACK TABLE statement but you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Its crucial to record the current SCN before issuing a FLASHBACK TABLE clause.

In order a user to flash back a table to an earlier SCN or timestamp, or before drop you must have either :
1.FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege.
2. SELECT, INSERT, DELETE, and ALTER object privileges on the table.
3.Row movement must for tables in the Flashback list.

e.g
create table test (a int);
alter table test enable row movement;
insert into test values(1);
insert into test values(2);
insert into test values(3);
commit;


select dbms_flashback.get_system_change_number from dual;

8657840224 -- SCN before delete

delete from test where a=3;

commit;

select dbms_flashback.get_system_change_number from dual;

8657840556 -- SCN after delete

select * from test;
1
2

flashback table test to scn 8657840224; -- Restore table to before delete !!!

select * from test;

1
2
3

flashback table test to scn 8657840556; -- Restore table to after delete !!!

select * from test;

1
2

flashback table test to timestamp(systimestamp - interval '3' minute);

select * from test;

1
2
3

Now we will drop the table and flashback from recycle bin.

drop table test;

flashback table test to before drop;

select * from test;

1
2

And as you can see we can go back again !!!

flashback table test to scn 8657974029;

select * from test;

1
2
3

If you specify the user-specified name, and if the recycle bin contains more
than one object of that name, then the database retrieves the object that
was moved to the recycle bin most recently.

drop table test;


create table test (a int);
alter table test enable row movement;
insert into test values(1);
commit;

select * from test;
1

drop table test;

flashback table test to before drop;

select * from test;
1

flashback table test to before drop rename to test1;

select * from test1;
1
2
3


Now lets take a look information recycle bin has.

drop table test1;

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

owner object_name original_name operation can_undrop can_purge
--------------------------------------------------------------------
AGIS BIN$eTA7dNdz5FfgQAkKeIc6fw==$0 TEST1 DROP YES YES


purge recyclebin; -- Clear recycle bin

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

no rows selected

flashback table test1 to before drop;

ORA-38305 Object not in RECYCLE BIN.

drop table test; -- Put table to recycle bin

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';


OWNER OBJECT_NAME ORIGINAL_NAME OPERATION CAN CAN
------------------------------ ------------------------------ -------------------------------- --------- --- ---
AGIS BIN$eTA7dNd05FfgQAkKeIc6fw==$0 TEST DROP YES YES

flashback table test to before drop;


select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

no rows selected

drop table test purge; -- Permanently drop table

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';


no rows selected


Amazing 10g recovering features with no backup recover !!!!!

Tuesday, November 24, 2009

Reorginize table online using primary key

From Oracle9i forward, table re-organizations and redefinitions can be performed online.

What can be Redefined ONLINE on a Table?

1) A non-partitioned table can be converted into a partitioned table, and
vice versa
2) The organization of a table can be changed from a heap based to IOTs and vice versa
3) Non-primary key columns can be dropped,although this can be also done with sql
4) New columns can be added to a table,although this can be also done with sql
5) Existing columns can be renamed,although this can be also done with sql
6) Parallel support can be added or removed,although this can be also done with sql
7) Storage parameters can be modified,although this can be also done with sql

Restrictions

The table to be re-organized:

1) Cannot have User-defined data types
2) Cannot have FILE or LONG columns
3) Cannot be clustered
4) Cannot be in the SYS or SYSTEM schema
5) Cannot have materialized view logs and/or materialized views defined on them
6) Must be re-organized within the same schema


Example

Orig table : Table with hash 256 partitions , with pk , unique index , and fk
Target table : Non partitioned table

Connect as user with the privileges mentioned above.


1) Check if ORIG_TABLE can be redifined online
exec DBMS_REDEFINITION.can_redef_table('MYSCHEMA','ORIG_TABLE');
If procedure fails check ora error and apply solution if possible.

2) Create interim table

create table I_ORIG_TABLE as select * from ORIG_TABLE where 1=2;

3) Start online redefinition

exec DBMS_REDEFINITION.START_REDEF_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

4) Add constraints and indexes

ALTER TABLE MYSCHEMA.i_ORIG_TABLE ADD (
CONSTRAINT I_ORIG_TABLE_PK
PRIMARY KEY
(PK_COLUMN));

ALTER TABLE MYSCHEMA.i_ORIG_TABLE ADD (
CONSTRAINT i_ORIG_TABLE_FK
FOREIGN KEY (REF_COL)
REFERENCES MYSCHEMA.OTHER_TABLE (PK_COL)
DEFERRABLE INITIALLY IMMEDIATE);

alter table i_ORIG_TABLE MODIFY CONSTRAINT i_ORIG_TABLE_FK
DISABLE KEEP INDEX;

CREATE UNIQUE INDEX MYSCHEMA.i_ORIG_TABLE_IDX01 ON MYSCHEMA.ORIG_TABLE
(COL1, COL2);

5) Sync interim with original table

exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

Do this a lot of times before finish redefinition in order to apply dml during reorganization.

6) Find orig_table grants

select * from DBA_TAB_PRIVS where table_name='ORIG_TABLE';

Give the same grants to the users

e.g

grant select on I_ORIG_TABLE to user;

7) Finish redefinition

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

8) Drop old table and rename constraints and indexes.

drop table I_ORIG_TABLE cascade constraints purge; -- At this point old ORIG_TABLE has been renamed to interim table name.

alter table ORIG_TABLE rename constraint I_ORIG_TABLE_PK to ORIG_TABLE_PK;

alter index I_ORIG_TABLE_PK rename to ORIG_TABLE_PK;

alter table ORIG_TABLE rename constraint i_ORIG_TABLE_FK to ORIG_TABLE_FK;

alter index I_ORIG_TABLE_IDX01 rename to ORIG_TABLE_IDX01;

9) Gather statistics if needed

begin
dbms_stats.gather_table_stats(ownname=>'MYSCHEMA',tabname=>'ORIG_TABLE',cascade=>true,estimate_percent=>5);
end;
/

Now you have an new unpartitioned table with all constraints and indexes and without partitions.

Tuesday, November 17, 2009

ORA-27477 Creating a scheduler job.

When the name of the job is the same with an object name in the same schema of the creator of the job you get the ORA-27477 %s.%s already exists error in the EM job page.

Change the job name in order not to match an existing schema object name.

Thursday, November 12, 2009

Oracle row migration and chaining

A very nice article about row migration and chaining.

http://www.akadia.com/services/ora_chained_rows.html

Wednesday, November 11, 2009

enq: TX - allocate ITL

When you see this enqueue wait event in the dbconsole performance page or in the top 5 timed waits event seems that too many concurrent DML transactions are competing for the same data block.

Usually an increase in the value of initrans or maxtrans for the table and the indexes fixes the problem.

You can also try to move the table to a tablespace with smaller block size and reduce the parallel degree of the table and indexes.

Oracle memory usage AIX

The following link contains a collection of scripts that calculate oracle memory usage on AIX.

mem.zip :  https://drive.google.com/file/d/0B8hdAvkza6deal9SeFBzWDg2MkU/edit?usp=sharing


The file mem.rar contains:

instance_mem_use.sh
This script is used to measure memory usage of oracle instance asm instance , background processes , dedicated processes and also dbconsole processes.

Usage: instance_mem_use.sh dbsid dbname

In most cases dbsid=dbname but if you have a RAC node the dbsid differs from dbname.

Sample output :

/home/oracle $./instance_mem_use.sh RMINTPRD2 RMINTPRD
Memory Analysis for RMINTPRD2

Oracle Instance Memory
-----------------------------------------------------
System Global Area Memory Usage :5008MB
Program Global Area Memory Usage :500MB
-----------------------------------------------------

Oracle Processes Memory
-----------------------------------------------------
ORACLE Background Processes : 29
Memory Usage : 435MB

ORACLE Dedicated Processes : 154
Memory Usage : 1318MB

ORACLE dbconsole Processes : 1
Memory Usage : 5MB
-----------------------------------------------------


#####################################################
RMINTPRD2 Total Memory Usage :7268MB
#####################################################

other_mem_use.sh

This script is used to measure LISTENER,RMAN or CRS processes.

Usage: other_mem_use.sh LISTENER or CRS or RMAN

Sample otuput :

/home/oracle $ ./other_mem_use.sh CRS

-----------------------------------------------------
ORACLE CRS Processes : 18
CRS Total Memory Usage :202MB
-----------------------------------------------------

These scripts are used for the caclucations.

mem.awk
mem_calc.awk



References

Metalink note 123754.1 : AIX: Determining Oracle memory usage on AIX

Tuesday, November 10, 2009

Oracle on AIX Memory Issues

When using Oracle on AIX and while time passes database gets slower you must look the amount of memory that AIX uses for filesystem cache. This is not the fact if you user raw devices or filesystem accessed by CIO or DIO.

Examples from AIX 5.3

As root :

#svmon -G

svmon -G
size inuse free pin virtual
memory 4096000 3483045 612955 426604 3312529
pg space 2064384 62882

work pers clnt
pin 426507 0 97
in use 3306854 22 176169

PageSize PoolSize inuse pgsp pin virtual
s 4 KB - 1297893 62882 361900 1127377
m 64 KB - 136572 0 4044 136572


Here the memory used for filessystem cache is : ((176169+22)/4096000)*100 = 4%

My settings on this system are :

maxperm% = 10
minperm% = 5
strict_maxperm = 1
maxclient% = 10
strict_maxclient = 1
lru_file_repage = 0
v_pinshm = 0
maxpin% = 80

And LOCK_SGA set to false.

These settings was proposed by sysadmin having experience on AIX 5.3.
Although i was recommend to use v_pinshm=1 and LOCK_SGA=true we are testing the previous settings and we are having no problems.

So my recomendation is to start with the settings i post in discuss with your sysadmin and if you do not have the optimal performance try v_pinshm=1 and LOCK_SGA=true.

Server configuration :

3 Node RAC using shared RAW Devices and ASM plus GPFS and NFS for backup.
Each server has 16Gb of RAM and 2 RAC database instances each.

You can also check the following IBM resources.

VMM Tuning Tip: Protecting Computational Memory
http://www.ibm.com/developerworks/wikis/download/attachments/53871915/VMM+Tuning+Tip+-+Proctecting+Comp+Memory.pdf?version=2

AIX 5.3 Peformance Mangement Guide
http://publib.boulder.ibm.com/infocenter/pseries/v5r3/topic/com.ibm.aix.prftungd/doc/prftungd/prftungd.pdf

Configuring IBM General Parallel File System (GPFS) with Oracle RAC http://www.ibm.com/servers/enable/site/peducation/wp/zb726/zb726.pdf

Optimizing AIX 5L performance: Tuning your memory settings
http://www.ibm.com/developerworks/views/aix/libraryview.jsp?search_by=Tuning+your+memory+settings

Support for pinned memory
http://publib.boulder.ibm.com/infocenter/systems/index.jsp?topic=/com.ibm.aix.prftungd/doc/prftungd/support_pinned_mem.htm

Thursday, October 29, 2009

Oracle lock_sga parameter tips

A nice article by Burleson Consulting

http://www.dba-oracle.com/p_lock_sga.htm

Monday, October 26, 2009

Stop dbconsole job hung in status Stop Pending

Some times a job run from EM Console usually backup when you try to stop it it has a status : "Stop Pending". Until this status changes you can not delete the job and the job may hung on this status. If this happens do the following :

Loging as sysman :

select job_id, job_name, job_owner from mgmt_job where job_name like '%%';

exec mgmt_job_engine.stop_all_executions_with_id('');

select EXECUTION_ID, status from MGMT_JOB_EXEC_SUMMARY
where job_id ='' ;

Stopped status=8 , but you may see other statuses. If so check for EM the job status. If its still Stop Pending then force the stop

exec mgmt_job_engine.stop_all_executions_with_id('',TRUE);

Now check the status either by running the query or using the EM and the delete the job from EM console

Thursday, October 22, 2009

Clearing dbconsole agent state

Some times dbconsole fails to start if it has crashed unexpected. Mostly this is an agent issue that can not start or can not upload some files. Then you can try to clearstate agent.

export ORACLE_HOME=AGENT_HOME. This is usually the db home if you are using dbconsole.
If you are using Grid Control Agent has its own home.

emctl stop agent.

cd $ORACLE_HOME/host_SID/sysman/emd

rm agntstmp.txt lastupld.xml
rm upload/*
rm state/*
emctl clearstate agent
emctl start agent

After few minutes issue

emctl status agent

and you must see a similar output :

Last successful upload : 2009-10-22 07:31:47
Total Megabytes of XML files uploaded so far : 4.42
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 24.94%


And you are ok.

Monday, October 19, 2009

Primary Keys , Numbers or Characters

A very good article about this battle on ASK Tom Site.

Surrogate versus Natural Keys
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:689240000346704229


Identifying a Primary Key
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:708398700346544598

Thursday, October 15, 2009

Oracle 10gR2 and XML

A very interesting article on OTN about XML features.

http://www.oracle.com/technology/pub/articles/quinlan-xml.html

Wednesday, October 7, 2009

Stop mview from auto refreshing

A materialized view create with the automatic refresh can not be alter to stop refreshing. In order to do that you must broke the dbms_job that was created in order to refresh the view.

Connect as mview owner.

select * from user_jobs where broken ='N';

In the WHAT column for the mview refresh job you will see :

dbms_refresh.refresh('"[OWNER]"."[MVIEW_NAME]"');

Take the JOB_ID form the job column and execute:

begin
dbms_job.broken(JOB_ID,TRUE);
commit;
end;
/

And the mview will stop refreshing automatically.

If you want the mview to start refreshing again just run the job.

begin
dbms_job.run(JOB_ID);
commit;
end;
/

Then the mview will refresh and continue refreshing by the interval.

Monday, September 14, 2009

ORA-600 [32695] [hash aggregation can't be done] 10.2.0.3

Bug 5893340 : ORA-600 [32695] [hash aggregation can't be done] can occur
for a GROUP BY query if hash aggregation is chosen.
Fixed on 10.2.0.4 and up.

But

Bug 6471770 :

Group-By queries can fail with ora-32690 or
ORA-600 [32695] [hash aggregation can't be done]
when operating on a large volume of data if hash group by
aggregation is used.

Also, it is possible for hash group-bys to over allocate memory beyond
the limit impose by the auto-memory manager.

Fixed in 10.2.0.5 and up


In order to avoid the erros from both bugs :

1) Disable Hash group-by by setting
"_gby_hash_aggregation_enabled" to FALSE,

2) Use NO_USE_HASH_AGGREGATION hint on your query

Friday, September 11, 2009

Change sysman and dbsnmp password for dbconsole 10G

1) Change sysman password:

On node the dbconsole is running

$ export ORACLE_SID=MYDB
$ emctl stop dbconsole --If you have RAC stop dbconsole on other(s) node(s) also.

$ sqlplus sys as sysdba

SQL>alter user sysman identified by newpass;

SQL>exit;

If you are on 10.2.0.4 and up
$ emctl setpasswd dbconsole
   provide the new password

Prior 10.2.0.4
Go to $ORACLE_HOME/host_sid/sysman/config (if RAC on each node)
Save the file emoms.properties to emoms.properties.orig
Edit the file emoms.properties
Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwd=
Replace the encrypted value by the new password value
Search for the line:
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
Replace TRUE by FALSE

$ emctl start dbconsole --If you have RAC start dbconsole on the other(s) node(s) also.

Check that the password has been encrypted
Edit the file $ORACLE_HOME/host_sid/sysman/config/emoms.properties (if RAC on each node)
Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwd=
Check that the password is encrypted
Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwdEncrypted=
Check that the value is TRUE

2) Change dbsnmp password:

On node the dbconsole is running
$ export ORACLE_SID=MYDB
$ emctl stop dbconsole --If you have RAC stop dbconsole on other(s) node(s) also.
$ sqlplus sys as sysdba
SQL>alter user dbsnmp identified by newpass;
SQL>exit;

Go to $ORACLE_HOME/host_sid/sysman/emd (if RAC On each node):
Save the file targets.xml to targets.xml.orig
Edit the file targets.xml
Find all occurrences of
Property NAME="password" VALUE="" ENCRYPTED="TRUE"
Replace the encrypted value by the new password value
Replace TRUE by FALSE

On node the dbconsole is running
$ emctl start dbconsole --If you have RAC stop dbconsole on other(s) node(s) also.

References
Oracle Support notes : 259379.1 & 259387.1

Tuesday, September 8, 2009

IBM GPFS and Oracle RAC 10gR2 Tips on AIX 5.3

Here is some tuning tips for GPFS and Oracle RAC

1) Do not use the "dio" mount option for the GPFS file system

2) If using RAID devices, configure a single LUN for each RAID device. Do not create LUNs across RAID devices for use by GPFS as this will ultimately result in significant loss in performance as well as making the removal of a bad RAID more difficult. GPFS will stripe across the multiple LUNs (RAIDs) using its own optimized method.

3) For Oracle RAC databases, set the GPFS file system block using the "mmcrfs" command and the "-B" option, to a large value using the following guidelines:512 KB is generally suggested.

4) GPFS Threads
Use the following guidelines to set the GPFS "worker threads" to allow the maximum parallelism of the Oracle AIO threads, and the GPFS "prefetch threads" to benefit Oracle sequential I/O.
On a 64-bit AIX kernel:
GPFS worker threads can be <= 548.
GPFS worker threads + GPFS prefetch threads <= 550.
When requiring GPFS sequential I/O, set the prefetch threads between 50 and 100 (the default is 64), and set the worker threads to have the remainder.
Example:
"mmchconfig prefetchThreads=75"
"mmchconfig worker1Threads=475"

5) For Oracle RAC node recovery to work correctly, GPFS must be configure to be automatically loaded at boot time and automatically mount the GPFS file systems. Use the following two GPFS commands to configure this:
root@raven:64bit /> mmchconfig autoload=yes
root@raven:64bit /> mmchfs /dev/oragpfs -A yes
mmchfs: 6027-1371 Propagating the changes to all affected nodes.
This is an asynchronous process.


References : Oracle Metalink Document : 302806.1

Wednesday, September 2, 2009

ORA-01429 Index-Organized Table: no data segment to store overflow row-pieces

When you get this error probably you have exceeded the maximum length of an IOT record without specifying the overflow segment. The maximum length of an IOT record, in order to fit into the index block, is limited to half the
block size

Tuesday, September 1, 2009

Transportable Table Space (TTS)

Transportable Table Space (TTS) is used to take out of the database pieces of data for various reasons (Archiving , Moving to other databases etc). From 10g onwards you can also transport a tablespace across different platforms of O/S using rman to convert the tablespace to the desired endian.

The following steps covering the TTS for an ASM tablespace named DATA.
I will also use the rman convert command to so how to move tablespaces across platforms. Finally i will drop the original tablespace reimport the exported and convert it to asm

1) Check that the tablespace will be seft contained

As sys
SQL> execute sys.dbms_tts.transport_set_check('DATA',TRUE);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

If you have no rows selected you have a self contained tbs and you can proceed.
Otherwise you must fix the errors before proceed.

2) Make the tablespace read only

alter tablespace data read only;

3) Export the metadata using data pump

SQL> create or replace directory tts_dir as '/data/ttbs';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY tts_dir to system;

Grant succeeded.

expdp system DUMPFILE=data.dmp DIRECTORY = tts_dir TRANSPORT_TABLESPACES=DATA TRANSPORT_FULL_CHECK=Y

If the tablespace set being transported is not self-contained, then the export will fail.

And check the metadata file.

[oracle@labdb01 ttbs]$ ls -la /data/ttbs/
total 512
drwxr-xr-x 2 oracle oinstall 4096 Sep 1 15:03 .
drwxr-xr-x 5 26 26 4096 Sep 1 14:13 ..
-rw-rw---- 1 oracle oinstall 81920 Sep 1 15:04 data.dmp
-rw-rw-r-- 1 oracle oinstall 1061 Sep 1 15:04 export.log

4) Use V$TRANSPORTABLE_PLATFORM to find the exact platform name of the
target database. You can execute the following query on target platform
instance or in your instance if you want just to take out the file and reimport it later.

SQL> set line 200

SQL> SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30) PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------------------------------------------------------------------ --------------
13 Linux x86 64-bit Little
SQL>

5) Use rman to convert and copy the datafile from the ASM to the directory where the metadata exist (/data/ttbs)

rman target /
RMAN> CONVERT TABLESPACE DATA TO PLATFORM 'Linux x86 64-bit' FORMAT '/data/ttbs/%U';

ls -la /data/ttbs
drwxr-xr-x 2 oracle oinstall 4096 Sep 1 15:11 .
drwxr-xr-x 5 26 26 4096 Sep 1 14:13 ..
-rw-rw---- 1 oracle oinstall 81920 Sep 1 15:04 data.dmp
-rw-r----- 1 oracle oinstall 52436992 Sep 1 15:11 data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn
-rw-rw-r-- 1 oracle oinstall 1061 Sep 1 15:04 export.log

And your done with the export

Import the tablespace and convert it to asm

1) Drop the orginal tablespace

SQL> drop tablespace data including contents;

Tablespace dropped.

2) Use impdp to import it

impdp system DUMPFILE=data.dmp DIRECTORY = tts_dir TRANSPORT_DATAFILES='/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn'

SQL> alter tablespace data read write;

Tablespace altered.

Now the tablespace is accessible from the database but its not in the asm

3) Use RMAN to transfer the tablespace to ASM

rman target /
RMAN> backup as copy datafile '/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn' format '+DATA';

If the 10g database is open you need to offline the datafile first

SQL> alter database datafile '/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn' offline;

Database altered.

Switch to the copy:
RMAN> switch datafile '/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn' to copy;

using target database control file instead of recovery catalog
datafile 75 switched to datafile copy "+DATA/rlabdb/datafile/data.341.696439773"

RMAN> recover datafile '+DATA/rlabdb/datafile/data.341.696439773';

Starting recover at 01-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=523 instance=rlabdb1 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 01-SEP-09

SQL> alter database datafile '+DATA/rlabdb/datafile/data.341.696439773' online;

Database altered.

And you are done !!!

Create IOTs Examples

Some examples on how to create Index Organized tables

1) create table iot_table (a int , b int, primary key (a))
organization index;

2) create table iot_table (a int , b int, primary key (a,b))
organization index tablespace users;

3) create table iot_table (a int , b int, constraint iot_table_pk primary key (a,b))
organization index tablespace users;

Friday, July 3, 2009

Oracle Scheduler Examples

1) Job that runs every minute available to start immediately

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TST"."TEST_JOB_PER_MIN"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
TST12.MY_PROC;
end;',
repeat_interval => 'FREQ=MINUTELY',
start_date => systimestamp at time zone 'UTC',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

2) Job that runs every 10 minutes starting at 11:45am UTC

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"MYSCHEMA"."MY_JOB_NAME"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
MY_PROC;
end;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => to_timestamp_tz('2010-04-13 11:45:00 0:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'),
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

3) Job that runs every 2 hours starting at 11:45am UTC.

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"MYSCHEMA"."MY_JOB_NAME"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
MY_PROC;
end;',
repeat_interval => 'FREQ=HOURLY;INTERVAL=2',
start_date => to_timestamp_tz('2010-04-13 11:45:00 0:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'),
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/


4) Job that runs once a day every day and starts at 04/07/2009 03:00 am UTC.

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TST"."TEST_JOB_PER_DAY"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
TST12.MY_PROC;
end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0',
start_date => to_timestamp_tz('2009-07-04 UTC', 'YYYY-MM-DD TZR'),
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

5) Job that runs every day twice,at specific times UTC
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'MY_USER.JOB_NAME',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update mytable set x=1;
commit;
end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=16,22;BYMINUTE=10;BYSECOND=0',
start_date => systimestamp at time zone 'UTC',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

Thursday, July 2, 2009

ORA-12170 & SQLNET.INBOUND_CONNECT_TIMEOUT

SQLNET.INBOUND_CONNECT_TIMEOUT has been introduced in version 9i.This has to be configured in sqlnet.ora file specifies the time,in seconds, for a client to connect with the database server and provide the necessary authentication information.

If there are network latencies and the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection logs the IP address of the client and an ORA-12170: TNS:Connect timeout occurred error message to the sqlnet.log file.
The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message. In version 10g and higher, the ORA-3136 errors may appear in the alert.log.


To protect both the database server and the listener for denial of service attacks
Oracle Corporation recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file.

TIPS

In 10gR2, the default setting for these parameters is 60 seconds.
So set the parameters to at least that value in pre-10gR2 releases because there is no default setting for this versions.

E.g

Set on sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=60

Set on listener.ora for each listener
INBOUND_CONNECT_TIMEOUT_[LISTENER_NAME]=60

Wednesday, June 24, 2009

Hash Partitioning tables on a db migrated to RAC

Generally partitioning is not ideal for high OLTP environments. In some cases may be useful to overcome contention problems on a single hot object modified all the time by increasing concurrency. So instead of having a single table segment with single index segment you can split it more segments using partitioning. I used hash partitioning to overcome contention problems on a single instance database that was migrated to a 3 node RAC database.

When I run performance tests on the single instance database I saw contention on some hot objects (log application tables). When this database migrated to RAC the problems were multiplied by the cluster wait events due to inter-instance block communication and locks on the shared SGA. This had a 30% percent drop of the TPS the RAC database could do compared the single instance using the same application version.

So I used hash partitioning on the primary key column using 64 and 128 partitions depending the object growth prediction. I also partitioned all indexes. After doing that the rac database had performed as the single instance and more TPS is depending on further improvement of the application.

Hash Partitioning

- A hash function is applied to the partition key to determine in which of the N partitions the data should be placed.

- The hash partition key chosen for a table should be a column or set of columns that are unique (Pks are the best) and if this not possible
must have as many distinct values as possible.

- Oracle recommends that N number of partitions must be a power of 2 (2,4,8,16,32,64 etc) in order to achieve the best overall distribution.

Monday, June 1, 2009

Index Organized Tables - IOTs

IOTs are an Oracle table structured introduced since 9i. They are tables stored in an index segment with their data stored sorted according to their primary key.

e.g

create table iot(a int , b int ,c int ,d int, primary key (a,b)) organization index;

When to use :

1) When having selects that accessing a table always by using indexed columns on the where clause:

e.g

select b,d,c from iot where a=100;

2) Similar for tables that are accessed exclusively by their primery key:

e.g

select d,c from iot where a=100 and b=10;

3) When you want to ensure that your data are stored in a specific order (by PK). This is usefull to physically co-locate child data.

4) For tall "skinny" tables. Tables with small number of colums.

When not to use:

1) For tables that are have lots of inserts.
2) For tables with large number of columns
3) For potentially large and very large tables
4) Not in data warehouse and datamart schemas. Allthough you can use them for small child tables in such schemas.

Monday, May 11, 2009

Change ports for dbconsole componets

In order to change ports for the dbconsole componets do:

Set ORACLE_HOME & ORACLE_SID

For non RAC : emca -reconfig ports -DBCONTROL_HTTP_PORT 1158 -AGENT_PORT 3938 -RMI_PORT 5521 -JMS_PORT 5541

for RAC : emca -reconfig ports -cluster -DBCONTROL_HTTP_PORT 1158 -AGENT_PORT 3938 -RMI_PORT 5521 -JMS_PORT 5541

Thursday, May 7, 2009

Oracle RAC 10g : Backing up CSS and OCR files

First of all find the location of the css and ocr files:

Having set up the crs home

Voting File (CSS) : crsctl query css votedisk

If the file resides on shared filesystem (NFS,OCFS etc) then the you will see the ful path e.g /u02/oradata/css/cssfile

Now copy the file : cp /u02/oradata/css/cssfile /home/oracle/cssfilebackup

CSS on raw device:crsctl query css votedisk
0. 0 /dev/rhdisk5

located 1 votedisk(s).

In order to backup the CSS:
dd if=/dev/rhdisk5 of=/home/oracle/cssbackup


OCR File : ocrcheck
Here the ocr is on shared file system.
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 6108
Available space (kbytes) : 256012
ID : 353255034
Device/File Name : /u02/oradata/ocr/ocrfile
Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Here the ocr is on raw device
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 1048300
Used space (kbytes) : 7252
Available space (kbytes) : 1041048
ID : 752511649
Device/File Name : /dev/rhdisk3
Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded


First you find out if automatic backup is running.
ocrconfig -showbackup
labdb01 2009/05/07 07:23:47 /u01/app/crs/cdata/testcluster
labdb01 2009/05/07 03:23:47 /u01/app/crs/cdata/testcluster
labdb01 2009/05/06 23:23:47 /u01/app/crs/cdata/testcluster
labdb01 2009/05/05 23:23:45 /u01/app/crs/cdata/testcluster
labdb01 2009/04/30 03:23:32 /u01/app/crs/cdata/testcluster

So you have a week of backup of ocr.
Now you can take a bakcup for the ocr for extra reduduncy :

As root : ocrconfig -export /home/oracle/ocrbackup -s online

Remember to take ocr & voting disks backup after any changing in the cluster registry.
(Add/Remove service , node , instance etc)

Wednesday, April 29, 2009

ORA-4030

This error indicates that the oracle server process is unable to allocate more memory from the operating system.This memory consists of the PGA
This could be caused by your process itself, like your process is just requesting too much memory, or some other reasons cause the operating system memory to be depleted, like a too big SGA or too many processes to be accomadated for the systems virtual memory (physical memory + swap space).

To resolve this error check the following:

1) Is there still sufficient memory available?
2) Is there an operating system limit set?
3) Is there an oracle limit set?
4) Which process is requesting too much memory?
5) How to collect information on what the process is actually doing?

References : Metalink Note 233869.1

Identify the usage of pfile or spfile

In oracle 9i and up spfile is the preferred way to start the instance.
In order to see if an instance has started with pfile or spfile as sys using sqlplus run :

select decode(count(*), 1, 'spfile', 'pfile' ) init_file_type
from v$spparameter whe rerownum=1 and isspecified='TRUE';

Also you can use the show parameter spfile.

In order to create a spfile from pfile the instance must be down.
Then run

create spfile from pfile; to use the default location which is $ORACLE_HOME/dbs on Unix and %ORACLE_HOME%/database on Windows.

In order to create the pfile if the instance has started with spfile run:

Create pfile from spfile;

Friday, April 10, 2009

ORA-07445: exception encountered: core dump [kkdliac()

Create or Replace view as select may fail with the following error

ORA-07445: Exception aufgetreten: CORE Dump [kkdliac()+72] [SIGSEGV] [Address not mapped to
object] [0x000000004] [] []

Call stack:

ksedmp ssexhd sigacthandler kkdliac cvwdrv opiexe opiosq0 opiall0 opial7 opiodr ttcpip

To be seeing this bug the following must be true:
1. A core dump is seen in kkdliac() during a create or alter view operation
2. The view would have been compiled with compilation errors
3. The select list is of the form: (select-list-item(s)), (table).*


Cause
ORA-07445 [kkdliac] error reported while creating or altering the view using "Create.. as Select"
due to Bug 2678804. Bug is fixed in 11g RDBMS version.

Details:
Executing a "CREATE .. FORCE VIEW" against a non-existent table might core
dump if the select list contains (table).* where table is the
non-existent table and another select list item precedes the (table).* entry.


Solution
To avoid this error

1) Create the view without compilation errors, like avoid creating a view against a non-existent table, and

2) Use the complete column names in select list, instead of "table_name.*"

Friday, March 27, 2009

ORA-27054 on NFS file system on AIX for backup

On AIX 5.3 ML4 and up which is the supported version to be used with NAS devices and database 10gR2 allthough you create nfs filesystems rman reports the following error :

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

This is happenig because on Oracle 1gR2 Oracle checks the options with which a NFS mount is mounted on the filesystem.
Correct option for AIX datafiles and backup are :
cio,rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,vers=3,timeo=600

If you mount your NFS filesystems and you got on this error this is due to bug 5146667

If availiable apply patch. Else use the follwoing work arround:

1) set the Event 10298 in the init file event="10298 trace name context forever, level 32"

If you are using the spfile then :
SQL> alter system set event='10298 trace name context forever, level 32'scope= spfile sid='*';
Once you set the above parameter restart the instance.
Check as follows
SQL> select name, value from v$parameter where name = 'event';
NAME VALUE
---------- ------------------------------------------------------------
Event 10298 trace name context forever, level 32

Then try the backups again.

sid='*' is for RAC. For single instance do not use it.

Thursday, March 19, 2009

enq: US - contention

When you see enq: US - contention on Top 5 wait events on AWR or Statspack report or in ADDM findings you can take the following actions :

1) Check undo advisor if in 10g anf up.
2) Edit your undo retension parameter
3) Give space to Undo Tablespace
4) Move Undo tablespace to an other disk not so hot disk.

Mostly this wait event concerns autoxents on undo tablespaces.

Thursday, March 12, 2009

10gR2 RAC on AIX 5L Considerations

On AIX 5L (5.3 ML1 and up) in order to have an optimal rac configuration apply the follwoing settings

Network settings:

ipqmaxlen = 512 (Requires Restart)
rfc1323 = 1
sb_max = 2*udp_recvspac
tcp_recvspace = 65536
tcp_sendspace = 65536
udp_recvspace = 10* udp_sendspace
udp_sendspace = (DB_BLOCK_SIZE * DB_MULTIBLOCK_READ_COUNT) + 4 KB

To find out your settings run as root:

no -a | grep ipqmaxlen
no -a | grep rfc1323
no -a | grep sb_max
no -a | grep tcp_recvspace
no -a | grep tcp_sendspace
no -a | grep udp_recvspace
no -a | grep udp_sendspace

Oracle User Shell Limits

Soft FILE size = -1 (Unlimited)
Soft CPU time = -1 (Unlimited)
Soft DATA segment = -1 (Unlimited)
Soft STACK size -1 (Unlimited)

System Configuration Parameters
Maximum number of PROCESSES allowed for each user > =2048 or 128+PROCESS+PARALLEL_MAX_SERVERS

VMM Settings

AIX large page pool
vmo -p -o lgpg_regions=num_of_large_pages -o lgpg_size=16777216
num_of_large_pages = INT((total_SGA_size-1)/16MB)+1

VMM Settings
minperm% = 5
maxperm% = 90
maxclient% = 90
lru_file_repage = 0
minfree = 960
maxfree = 1088

In order to lock sga into memory
v_pinshm = 1
maxpin% = 80
Usually this is the max value to use, but you can also use ( (size of SGA / size of physical memory) *100) + 3 as more conservative setting.

The set LOCK_SGA=TRUE in spfile or init file.

To find your settings run as root
vmo -a grep lgpg_size
vmo -a grep lgpg_regions
vmo -a grep minperm%
vmo -a grep maxperm%
vmo -a grep maxclient%
vmo -a grep lru_file
vmo -a grep minfree
vmo -a grep maxfree
vmo -a grep v_pinshm
vmo -a grep maxpin%

Tuesday, February 17, 2009

Manually switch over to the standby database

Manually switch over of dataguard configuration without dataguard broker

1. Verify the Physical Standby Database Is Performing Properly
Step 1 Identify the existing archived redo log files on strandby

SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step 2 Force a log switch to archive the current online redo log file on primary
SQL>ALTER SYSTEM SWITCH LOGFILE;

Step 3 Verify the new redo data was archived on the standby database.
SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step 4 Verify that received redo has been applied on standby
SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

The value of the APPLIED column for the most recently received log file will be
either IN-MEMORY or YES if that log file has been applied.

2. Pre-Switchover Checks
Verify Managed Recovery is Running (non-broker) on the standby
SQL>select process from v$managed_standby where process like 'MRP%';

Cancel apply delay for the target standby using SQL
SQL>select delay_mins from v$managed_standby where process = 'MRP0';

if delay_mins >0 then
SQL>RECOVER MANAGED STANDBY DATABASE CANCEL
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Verify there are no large GAPS.
On primary
SQL>SELECT THREAD#, SEQUENCE# FROM V$THREAD;
On the standby the following query should be no more than 1-2 less than the primary query result
SQL>SELECT THREAD#, MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG val, V$DATABASE vdb
WHERE APPLIED = 'YES' AND val.RESETLOGS_CHANGE#=vdb.RESETLOGS_CHANGE#GROUP BY THREAD#;
If there is a gap .. fix the gap

Verify Primary and Standby TEMP Files Match
SQL>select tmp.name FileName, bytes, ts.name Tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;
If there is no match then you will correct the temp tbs problem after switchover

Verify that there is no issue with V$LOG_HISTORY on the Standby (bug 6010833)
1. Get the Primary RESETLOGS_CHANGE#. On the primary execute:
SQL>select RESETLOGS_CHANGE# from V$DATABASE;

2. On the standby get the maximum sequence numbers for each thread from V$LOG_HISTORY:
SQL>select thread#, max(sequence#) from V$LOG_HISTORY where resetlogs_change#=1 group by thread#;

3. Get current sequence numbers on standby:
SQL>select thread#, max(sequence#) from v$archived_log alog, v$database db where alog.resetlogs_change#=db.resetlogs_change# group by thread#;

4. The last sequence# for each thread# from V$LOG_HISTORY should be close (the difference in log sequences < 3) to the last sequence# for each thread# from V$ARCHIVED_LOG.

5. If there is an issue with V$LOG_HISTORY then recreate the standby controlfile

Verify no old partial Standby Redo Logs on the Standby (bug 7159505)
1. Identify any active standby redo logs (SRL’s)
SQL>SELECT GROUP#, THREAD#, SEQUENCE# FROM V$STANDBY_LOG WHERE STATUS = 'ACTIVE' ORDER BY THREAD#,SEQUENCE#;

2.Identify maximum applied sequence number(s).
SQL> select thread#, max(sequence#)
from V$LOG_HISTORY
where resetlogs_change#=<>
group by thread#;

3.If there are any active SRL's that have a thread#/sequence# less than the thread#/sequence# returned from the V$LOG_HISTORY (meaning the recovery has progressed beyond the active SRL) query then clear them.
SQL>RECOVER MANAGED STANDBY DATABASE CANCEL
SQL>ALTER DATABASE CLEAR LOGFILE GROUP ;

3 Switchover
Clear Potential Blocking Parameters & Jobs
1.Capture current job state on the primary
SQL>select * from dba_jobs_running;
SQL>select owner,job_name, start_date, end_date, enabled from dba_scheduler_jobs where enabled='TRUE' and owner <> 'SYS';
SQL>show parameter job_queue_processes -- and capture the value

Block further job submission
SQL>alter system set job_queue_processes=0 scope=both;
SQL>execute dbms_scheduler.disable(job_name);

Disable any cron jobs that may interfere , rman backups
Shutdown all mid-tiers (e.g db console)
$ emctl stop dbconsole

Monitor Switchover
Turn on Data Guard tracing on primary and standby
Tracing is turned on to have diagnostic information available in case any issues arise.
SQL>show parameter log_archive_trace -- capture the value
Set Data Guard trace level to 8191
SQL>alter system set log_archive_trace=8191;
Trace output will appear under the destination pointed to by the database parameter BACKGROUND_DUMP_DEST with “mrp” in the file name.

Monitor alert log for both primary and standby

Finally Switchover
Verify that the primary database can be switched to the standby role
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
A value of TO STANDBY or SESSIONS ACTIVE (requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role

Switchover the primary to a standby database
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
in primary alert log you would see

Switchover: Complete - Database shutdown required (sfs_stby1)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN

Verify that the standby database can be switched to the primary role
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Check if the standby has ever been open read-only
SQL> SELECT VALUE FROM V$DATAGUARD_STATS WHERE NAME='standby has been open';

If the standby was open read-only then restart the standby
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

Switchover the standby database to a primary
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Open the new primary database:
SQL> ALTER DATABASE OPEN;
Correct any tempfile mismatch

Restart the new standby
On the the new standby database (old production database), bring it to the mount state and start managed recovery.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Post-Switchover Steps
For each database:
SQL>alter system set log_archive_trace=;
SQL>alter system set job_queue_processes= scope=both;
SQL>execute dbms_scheduler.enable();
Enable any cron jobs that were diabled in 3.1

Check that they are syncronized
Perfom log switch on new primary
Create cron job to backup new primary !!!


Reference
Metalink Note id : 751600.1

Friday, February 13, 2009

Build Your Own Oracle RAC Cluster on Oracle Enterprise Linux and iSCSI

A very nice article form OTN. Can be used as a guide to build RAC on Linux using OCFS2 and ASM.

http://www.oracle.com/technology/pub/articles/hunter_rac10gr2_iscsi.html

Configuring VLANs Under Fedora/RHEL/CentOS

A nice link to help configure vlans on RHEL/CentOs. Especially usefull when you want to install and configure Oracle 10gR2 CRS using vlans for public, and cluster interconnect.

http://www.tummy.com/journals/entries/jafo_20061130_063931

Tuesday, February 10, 2009

Installing RAC 10gR2 on RHEL5.2 POWER LINUX

On both nodes
more /etc/oraInst.loc
on node1
groupadd oinstall
groupadd dba
useradd -u 200 -g oinstall -G dba oracle
passwd oracle
id nobody (user must exist)
id oracle (record the uid,gid)

on node2
groupadd -g 500 oinstall
groupadd -g 501 dba
useradd -u 200 -g oinstall -G dba oracle

Configure ssh equivelance
as root
vi /etc/hosts
127.0.0.1 localhost.localdomain localhost
172.18.253.32 billing2
172.18.253.30 billing1

which scp
if not in /usr/local/bin create link :
cd /usr/local/bin ; ln -s /usr/bin/scp scp

which ssh
if not in /usr/local/bin create link :
cd /usr/local/bin ; ln -s /usr/bin/ssh ssh

On both nodes login as oracle
mkdir .ssh
chmod 700 .ssh
ssh-keygen -t rsa
ssh-keygen -t dsa

on billing1:
cd .ssh
ssh billing1 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh billing1 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys
ssh billing2 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh billing2 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys
scp authorized_keys billing2:/home/oracle/.ssh/

on billing2:
cd .ssh
ssh billing1 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh billing1 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys
ssh billing2 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh billing2 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys
scp authorized_keys billing1:/home/oracle/.ssh/

on both nodes
chmod 600 ~/.ssh/authorized_keysexec
/usr/bin/ssh-agent $SHELL
/usr/bin/ssh-add
vi /home/oracle/.ssh/config ...
add
Host *
ForwardX11 no

vi .bashrc ... add
if [ -t 0 ]; then
stty intr ^C
fi
vi .bash_profile
umask 022
export DISPLAY=172.18.41.140:0
. ./.bash_profile

as root
vi /etc/hosts
127.0.0.1 localhost.localdomain localhost
172.18.253.32 billing2
172.18.253.33 billing2-v
10.254.254.32 billing2-i
172.18.253.30 billing1
172.18.253.31 billing1-v
10.254.254.30 billing1-i

vi /etc/hosts.equiv
billing1 oracle
billing2 oracle
billing1-i oracle
billing2-i oracle

Install required packages
yum install libaio-devel
yum install libXp

Download and install ibm xlc
http://www-1.ibm.com/support/docview.wss?rs=2030&context=SSJT9L&context=SSENT9&context=SSEP5D&dc=D400&dc=D410&dc=D420&dc=D430&q1=Run-time+Environment+Component&uid=swg24007906&loc=en_US&cs=utf-8&lang=en
rpm -ihv update/xlc/apr2007/xlsmp.msg.rte-1.5.1-3.ppc64pseries.rpm
rpm -ihv update/xlc/apr2007/xlsmp.rte-1.5.1-3.ppc64pseries.rpm
rpm -ihv update/xlc/apr2007/vacpp.rte-7.0.1-3.ppc64pseries.rpm
rpm -ivh vacpp.rte.lnk-7.0.1-0.ppc64pseries.rpm
rpm -ihv vac.lib-7.0.1-0.ppc64pseries.rpm

add entries to /etc/sysctl.conf
# Oracle RAC
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

sysctl -p

vi /etc/security/limits.conf
# Oracle Limits
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
# As per Release Notes
oracle soft memlock 3145728
oracle hard memlock 3145728

vi /etc/pam.d/login
# Oracle RAC
session required pam_limits.so

vi /etc/profile
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
mkdir -p /orabin
chown -R oracle:oinstall /orabin
chmod -R 775 /orabin
mkdir -p /orabin/crs
chown -R root:oinstall /orabin/crs
chmod -R 775 /orabin/crs
as root on both nodes
lsmod grep hang
if not hangcheck_timer configured then
insmod /lib/modules/2.6.18-92.el5/kernel/drivers/char/hangcheck-timer.ko hangcheck_tick=30 hangcheck_margin=180
add the previous comand in /etc/rc.d/rc.local

Configuring raw devices for ocr and css using mpath (Metalink Note:564580.1)
raw /dev/raw/raw1 /dev/mapper/mpath1
raw /dev/raw/raw2 /dev/mapper/mpath2
raw -qals -l /dev/raw/
dd if=/dev/zero of=/dev/raw/raw1 bs=1024 count=100
su - oracle
dd if=/dev/zero of=/dev/raw/raw1 bs=1024 count=100 --> Permission Denied Expected
dd if=/dev/zero of=/dev/raw/raw2 bs=1024 count=100
su - oracle
dd if=/dev/zero of=/dev/raw/raw2 bs=1024 count=100 --> Permission Denied Expected
raw /dev/raw/raw1 0 0
raw /dev/raw/raw2 0 0
vi /etc/rc.d/rc.local and add
##### Oracle Cluster Registry (OCR) devices#####
chown root:oinstall /dev/mapper/mpath1
chmod 660 /dev/mapper/mpath1
raw /dev/raw/raw1 /dev/mapper/mpath1
chown root:oinstall /dev/raw/raw1
chmod 660 /dev/raw/raw1
##### Oracle Voting disks#####
chown oracle:oinstall /dev/mapper/mpath2
chmod 660 /dev/mapper/mpath2
raw /dev/raw/raw2 /dev/mapper/mpath2
chown oracle:oinstall /dev/raw/raw2
chmod 660 /dev/raw/raw2
/etc/rc.local
ll /dev/mapper
brw-rw---- 1 root oinstall 253, 5 May 30 14:40 mpath1
brw-rw---- 1 oracle oinstall 253, 6 May 30 14:40 mpath2
ls -l /dev/raw/
crw-rw---- 1 root oinstall 162, 1 May 30 15:41 raw1
crw-rw---- 1 oracle oinstall 162, 2 May 30 15:41 raw2
Disable sendmail Service and reboot
ll /dev/mapper/ & ls -l /dev/raw same results as above

Open oracle session on first node
export ORACLE_BASE=/orabin
export ORACLE_HOME=/orabin/crs

cd /orasoft
Disk1/cluvfy/runcluvfy.sh stage -pre crsinst -n billing1,billing2
Ignore vipca Error
runInstaller -ignoreSysPrereqs (as per Metalink Note: 414163.1)
Ignore remote attach home error
as root first on node1 and then on node2 do:
/orabin/oraInventory/orainstRoot.sh
Before Running root.sh on each node apply fix for Bug 4679769
FAILED TO FORMAT OCR DISK USING CLSFMTas per Metalink Note:564580.1
as root first on node1 and then on node2 do:
cd /orasoft
unzip p4679769_10201_IBMPower.zip
cd 5682732/
ls -l /orabin/crs/bin/clsfmt.bin
-rwxr-xr-x 1 oracle oinstall 784750 Nov 11 2005 /orabin/crs/bin/clsfmt.bin
cp /orabin/crs/bin/clsfmt.bin /orabin/crs/bin/clsfmt.bin.bak
cp clsfmt.bin /orabin/crs/bin/clsfmt.bin
chmod 755 /orabin/crs/bin/clsfmt.bin
ls -l /orabin/crs/bin/clsfmt.bin-rwxr-xr-x 1 oracle oinstall 778072 May 30 16:12 /orabin/crs/bin/clsfmt.bin
Apply workaround #2 for vipca errors as per Metalink Note:414163.1 (maybe not applying on ppc)
as root first on node1 and then on node2 do:
vi /orabin/crs/bin/vipca
Find lines : if [ "$arch" = "i686" -o "$arch" = "ia64" -o "$arch" = "x86_64" ]
then
LD_ASSUME_KERNEL=2.4.19
export LD_ASSUME_KERNEL
fi
unset LD_ASSUME_KERNEL <<== Line to be added
as root first on node1 and then on node2 do:
/orabin/crs/root.sh
On node2 you may see :
Running vipca(silent) for configuring nodeapps
The given interface(s), "eth0" is not public.
Public interfaces should be used to configure virtual IPs.
Ignore vipca error and run it from master node (First test from failling node failed, According to netapp document run from master node)
on node2:
export DISPLAY=172.18.41.140:0
/orabin/crs/bin/vipca
define interface for the virtual ips
and check crs after vipca finishes and installation reports finished
on both nodes
/orabin/crs/bin/crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....ng1.gsd application ONLINE ONLINE billing1
ora....ng1.ons application ONLINE ONLINE billing1
ora....ng1.vip application ONLINE ONLINE billing1
ora....ng2.gsd application ONLINE ONLINE billing2
ora....ng2.ons application ONLINE ONLINE billing2
ora....ng2.vip application ONLINE ONLINE billing2

Reboot just to check if all is ok !!!

FINISHED !!!!!!

Wednesday, February 4, 2009

ORA-10631 When Trying To Shrink A Table That Has A Function-based Index

The shrink_clause 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.

In order to shrink segments that have function based indexes , get index ddl , drop the index , shrink the table and recreate the index.

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.