Oracle in World: October 2008

Friday, October 31, 2008

Example of global partitioned, global non-partitioned and local Indexes

Index on the partitioned table can be of three types.
1)Global Non-partitioned Index.
2)Global Partitioned Index.
3)Local Partitioned Index.

With an example I will make you clear of these three different types of indexes on the partitioned table.

1)Create a partitioned table.


CREATE TABLE test_partition
(
id number,
created_date  date,
col3 varchar2(20)
)
PARTITION BY RANGE (created_date)
   (
    PARTITION part1 VALUES LESS THAN (to_date('11-oct-2008','dd-mon-yyyy')) ,
    PARTITION part2 VALUES LESS THAN (to_date('12-oct-2008','dd-mon-yyyy')) ,
    PARTITION part3 VALUES LESS THAN (to_date('13-oct-2008','dd-mon-yyyy')) ,
    PARTITION part4 VALUES LESS THAN (to_date('14-oct-2008','dd-mon-yyyy')) ,
    PARTITION junk VALUES LESS THAN (MAXVALUE)
  )
/
Table created.

Global Non Partitioned Index
This index span all over the table. Hence it is global and index is not partitioned.
SQL> create index tpid_i on test_partition(id);

Index created.


SQL> select index_name,partitioned, index_type from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     PAR INDEX_TYPE
------------------------------ --- ---------------------------
TPID_I                         NO  NORMAL

Local Partitioned Index

SQL> create index tpid_i on test_partition(id) local;

Index created.


SQL> select index_name,partitioned, index_type from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     PAR INDEX_TYPE
------------------------------ --- ---------------------------
TPID_I                         YES NORMAL


SQL> select partition_name, status from user_ind_partitions where
 2      index_name = 'TPID_I';

PARTITION_NAME                 STATUS
------------------------------ --------
JUNK                           USABLE
PART1                          USABLE
PART2                          USABLE
PART3                          USABLE
PART4                          USABLE

Global Partitioned Index


CREATE INDEX tpid_g ON test_partition(id)
  GLOBAL PARTITION BY RANGE (id)
     (PARTITION p1 VALUES LESS THAN (10),
      PARTITION p2 VALUES LESS THAN (20),
      PARTITION p3 VALUES LESS THAN (MAXVALUE));
Index created.
SQL> select index_name,partitioned, index_type from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     PAR INDEX_TYPE
------------------------------ --- ---------------------------
TPID_G                         YES NORMAL

Thursday, October 30, 2008

How to avoid rebuild of indexes if DDL performed on partitioned table

On the partitioned table, if we don't do anything with the index then the index against the partitioned table remain global Non partitioned indexes and thus whenever we perform any DDL operation against the table partition like drop a partition or truncate a partition or add a partition or merge a partition or split a partition then the associated global indexes become invalid. Thus after doing DDL we need to rebuild the indexes. This may be very cumbersome and it may need several hours if the table is big enough.

With an example I am demonstrating how global indexes against a table become invalid if I do any DDL against the table.

1)Create a partitioned table.


CREATE TABLE test_partition
 (
 id number,
 created_date  date,
 col3 varchar2(20)
 )
 PARTITION BY RANGE (created_date)
    (
     PARTITION part1 VALUES LESS THAN (to_date('11-oct-2008','dd-mon-yyyy')) ,
     PARTITION part2 VALUES LESS THAN (to_date('12-oct-2008','dd-mon-yyyy')) ,
     PARTITION part3 VALUES LESS THAN (to_date('13-oct-2008','dd-mon-yyyy')) ,
     PARTITION part4 VALUES LESS THAN (to_date('14-oct-2008','dd-mon-yyyy')) ,
     PARTITION junk VALUES LESS THAN (MAXVALUE)
   )
 /
Table created.

2)Insert data into the partitioned table.


insert into test_partition
    select rownum, to_date('10-oct-2008','dd-mon-yyyy')+mod(rownum,7),
           rownum
      from dba_users;

41 rows created.

3)Create index on the partitioned table.
SQL> create index tpid_i on test_partition(id);
Index created.

4)Be sure partitioned is done successful.
SQL> select count(*) from test_partition partition (part2);

COUNT(*)
----------
6

Let's see some sample data.


SQL> select * from test_partition partition (part2);
        ID CREATED_D COL3
---------- --------- --------------------
         1 11-OCT-08 1
         8 11-OCT-08 8
        15 11-OCT-08 15
        22 11-OCT-08 22
        29 11-OCT-08 29
        36 11-OCT-08 36

6 rows selected.

5)Let us see the status of the index against the partitioned table.
Note that since we have made global non-partitioned indexes so we will not get any entry about valid/unusable index in view user_ind_partitions. To get index status we have to query from user_indexes view.

SQL> select partition_name, status from user_ind_partitions where
index_name = 'TPID_I';
no rows selected


SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         VALID

6)Do some DDL operation on the partitioned table.
SQL> alter table test_partition truncate partition part2;
Table truncated.

Check by,
SQL> select count(*) from test_partition partition (part2);

COUNT(*)
----------
0

7)Check the index status now.


SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         UNUSABLE

Since we have global index so any DDL under the table marks the index unusable. If you want to make the index valid then you have to rebuild the index.

SQL> alter index tpid_i rebuild;
Index altered.



SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         VALID

This rebuild process really time consuming. And for production server it may not be tolerable.

8)The solution is to make the index as local partitioned index.
SQL> drop index tpid_i;
Index dropped.

SQL> create index tpid_i on test_partition(id) local;
Index created.

Check status by,



SQL>  select index_name, status from user_indexes where table_name='TEST_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         N/A

Since it is local partitioned index we have to query from user_ind_partitions.
SQL> select partition_name, status from user_ind_partitions where
2 index_name = 'TPID_I';

PARTITION_NAME STATUS
------------------------------ --------
JUNK USABLE
PART1 USABLE
PART2 USABLE
PART3 USABLE
PART4 USABLE

SQL> select count(*) from test_partition partition (part3);
COUNT(*)
----------
6

9)Let's do some DDL operation and test.
SQL> alter table test_partition truncate partition part3;
Table truncated.


SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         N/A


SQL>  select partition_name, status from user_ind_partitions where
  2      index_name = 'TPID_I';

PARTITION_NAME                 STATUS
------------------------------ --------
JUNK                           USABLE
PART1                          USABLE
PART2                          USABLE
PART3                          USABLE
PART4                          USABLE

And it still remains in USABLE status. So you might able to save a lots of time as well as non-interrupt service and effort against it.

Monday, October 27, 2008

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Error Description:
The scenario is I have created a materialized view through database link over a table reside on the remote database.

I used the ON COMMIT option and it fails with ORA-12054 as below.

SQL> create materialized view scott.mv_phones
2 TABLESPACE users
3 REFRESH FAST on commit
4 WITH rowid
5 AS select ID, PASS,
6 CONN_EXPIRY_DATE, CONN_STATUS
7 from PHONES@lnxdb where upper(IS_SOLD)='Y';
from PHONES@lnxdb where upper(IS_SOLD)='Y'
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Cause of the Problem
The ON COMMIT clause of CREATE MATERIALIZE VIEW has some restrictions. Such as,

•ON COMMIT clause is not supported for materialized views containing object types.

•With ON COMMIT clause inserting into the materialized view table(master table) is not supported by selecting rows from remote tables.

In our case we satisfy the second restriction and hence error comes.

Solution of the Problem
To implement the solution you have to provide ON DEMAND clause. You have to create as below.
SQL> create materialized view scott.mv_phones
2 TABLESPACE users
3 REFRESH FAST on demand
4 WITH rowid
5 AS select ID, PASS,
6 CONN_EXPIRY_DATE, CONN_STATUS
7 from PHONES@lnxdb where upper(IS_SOLD)='Y';

My 600th post in this Blog.

This is the 600th post published in this blog. Could not publish blogs regularly now a days because I am a bit busy with my consultancy.

Hopefully I will write regularly. Pray for me.

Any suggestion and feedback from you is really welcome.

Friday, October 24, 2008

Which Options are installed on your oracle database

There are various ways to know which options are installed on your oracle database. Below is some.
1)Using Oracle Universal Installer:
-Go to oracle database software installer.
-Under install folder run oracle universal installer. On windows it is oui.exe and on unix it is runIstaller.sh
-Select Installed Products.
-In the Inventory expand the selection and you can see list of options installed.

2)From V$OPTION:
From v$option view the column value's value TRUE means the corresponding option is installed/available and FALSE mean corresponding option is not installed/ not available.
SQL> set pages 100
SQL> col value for a5
SQL> set lines 120
SQL> select * from v$option;
PARAMETER VALUE
---------------------------------------------------------------- -----
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
.
.

Which indicates partition can be done or in other word partitioning feature is available but RAC is not installed or in other word RAC yet not available.

3)From DBA_REGISTRY:
In order to know which components are loaded into database and what is their current status issue,
SQL> col comp_name for a70
SQL> select comp_name, status from dba_registry;

COMP_NAME STATUS
---------------------------------------------------------------------- -----------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Workspace Manager VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle Expression Filter VALID
Oracle Data Mining VALID
Oracle Text VALID
Oracle XML Database VALID
Oracle Rules Manager VALID
Oracle interMedia VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
OLAP Catalog VALID
Spatial VALID
Oracle Enterprise Manager VALID

17 rows selected.

Sunday, October 19, 2008

ORA-00923: FROM keyword not found where expected

Convert Decimal to hexadecimal on Oracle

Way 01


SQL> create or replace package number_utils as
  2          function d_to_hex(decimal_num in integer) return varchar2;
  3          pragma restrict_references (d_to_hex, wnds, wnps, rnps);
  4  end;
  5  /
Package created.

SQL> create or replace package body number_utils as
  2  function d_to_hex (decimal_num in integer)
  3  return varchar2 is
  4          v_result       varchar2(12);
  5          v_hex_digit    varchar2(1);
  6          v_quotient     pls_integer;
  7          v_remainder    pls_integer;
  8  begin
  9          if (decimal_num < 10) then
 10                 v_result := to_char(decimal_num);
 11          elsif (decimal_num < 16) then
 12                 v_result := chr(65+(decimal_num-10));
 13          else
 14                 v_remainder := mod(decimal_num,16);
 15                 v_quotient  := round((decimal_num - v_remainder) /16);
 16                 v_result :=number_utils.d_to_hex(v_quotient) || number_utils.d_to_hex(v_remainder);
 17          end if;
 18          return v_result;
 19  end  d_to_hex;
 20  end number_utils;
 21  /
Package body created.

SQL> select number_utils.d_to_hex(7685) from dual;
NUMBER_UTILS.D_TO_HEX(7685)
--------------------------------------------------------------------------------
1E05

Way 02:


SQL> CREATE OR REPLACE FUNCTION dec_to_hex (decimal_val in number) RETURN varchar2 IS
  2    hex_num varchar2(64);
  3    digit  pls_integer;
  4    decimal_num pls_integer:=decimal_val;
  5    hexdigit  char;
  6  BEGIN
  7    while ( decimal_num > 0 ) loop
  8       digit := mod(decimal_num, 16);
  9       if digit > 9 then
 10          hexdigit := chr(ascii('A') + digit - 10);
 11       else
 12          hexdigit := to_char(digit);
 13       end if;
 14       hex_num := hexdigit || hex_num;
 15       decimal_num := trunc( decimal_num / 16 );
 16    end loop;
 17    return hex_num;
 18  END dec_to_hex;
 19  /
Function created.

SQL> select dec_to_hex(120) from dual;
DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78

Way 03:


SQL> CREATE OR REPLACE FUNCTION dec_to_hex (dec_num IN NUMBER) RETURN VARCHAR2 IS
  2   v_decin NUMBER;
  3   v_next_digit NUMBER;
  4   v_result varchar(2000);
  5  BEGIN
  6    v_decin := dec_num;
  7    WHILE v_decin > 0 LOOP
  8    v_next_digit := mod(v_decin,16);
  9  IF v_next_digit > 9 THEN
 10   IF v_next_digit = 10 THEN v_result := 'A' || v_result;
 11    ELSIF v_next_digit = 11 THEN v_result := 'B' || v_result;
 12    ELSIF v_next_digit = 12 THEN v_result := 'C' || v_result;
 13    ELSIF v_next_digit = 13 THEN v_result := 'D' || v_result;
 14    ELSIF v_next_digit = 14 THEN v_result := 'E' || v_result;
 15    ELSIF v_next_digit = 15 THEN v_result := 'F' || v_result;
 16    ELSE raise_application_error(-20600,'Untrapped exception');
 17   END IF;
 18  ELSE
 19   v_result := to_char(v_next_digit) || v_result;
 20  END IF;
 21  v_decin := floor(v_decin / 16);
 22  END LOOP;
 23  RETURN v_result;
 24  END dec_to_hex;
 25  /

Function created.

SQL> select dec_to_hex(17) from dual;

DEC_TO_HEX(17)
--------------------------------------------------------------------------------
11

Way 04:


SQL> create or replace function dec_to_hex(dec_num in number )
  2  return varchar2
  3  is
  4     l_str   varchar2(255) default NULL;
  5     l_num   number  default dec_num;
  6     l_hex   varchar2(16) default '0123456789ABCDEF';
  7          p_base   number:=16;
  8  begin
  9     if ( trunc(dec_num) <> dec_num OR dec_num < 0 ) then
 10             raise PROGRAM_ERROR;
 11     end if;
 12     loop
 13             l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
 14             l_num := trunc( l_num/p_base );
 15             exit when ( l_num = 0 );
 16     end loop;
 17     return l_str;
 18  end dec_to_hex;
 19  /

Function created.

SQL> select dec_to_hex(120) from dual;

DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78

Way 05:
Just using TO_CHAR function. Here the first argument is the decimal number. And the second argument is the format. In order to convert to decimal the format is 'XXXX'. Note that the format length must be greater enough so that the returned length in hexadecimal is not less than the format.

Example:
SQL> select to_char(120,'XXXX') from dual;
TO_CH
-----
78

SQL> select to_char(120000,'XXXXXXX') from dual;
TO_CHAR(
--------
1D4C0
select to_char(120,'XXXX') from dual;
Related Documents
http://arjudba.blogspot.com/2008/04/how-can-one-dump-examine-exact-content.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html

ORA-00600 internal error code, kmgs_pre_process_request_6

Error Description
In our production server while doing data pump import operation at the end of import it fails with following errors, then the instance crashed and database went down.
Errors in file /var/opt/dumpfile/bdump/arju_mman_23373.trc:
ORA-00600: internal error code, arguments: [kmgs_pre_process_request_6], [4], [2
], [0], [3], [0xC992C04D0], [], []
Sun Oct 19 18:16:20 2008
MMAN: terminating instance due to error 822
Instance terminated by MMAN, pid = 23373

In the trace file we got the call stack as,
ksedst, ksedmp, ksfdmp, kgerinv, kgeasnmierr, kmgs_pre_process_request, kmgsdrv, ksbabs, ksbrdp, opirip, opidrv, sou2o, opimai_real, main.

Cause of the problem
As we know in Oracle ORA-600 error series are oracle bug. This is oracle Bug 4433838. From alert log we see our MMAN (Memory Manager) process has been terminated. So something wrong happen with oracle MMAN.

This bug actually fires when the initialization parameter SGA_TARGET is set to an exact multiple of 4Gb. So, while setting big SGA be conscious.

A little bit more about this bug is if the SGA_TARGET value in HEXADECIMAL has 00000000 as the last bytes then you can hit this bug.

Detail Explanation of this bug
In out production server we set our SGA_TARGET=48G and this bug fires. We see 48G is multiple of 12 times of 4G. So this bug can error. A little bit elaborate of this issue,
Of 48G we get decimal value as,
SQL> col dec for 9999999999999
SQL> select 48*1024*1024*1024 dec from dual;
DEC
--------------
51539607552

Now let's get hexadecimal value of this number,
SQL> select to_char(51539607552,'XXXXXXXXXXXXX') hex from dual;
HEX
---------------
C00000000

As we see in the hexadecimal representation we get last 8 letters are all 0s; 00000000. The bug happens whenever in hexadecimal representation the 4 bytes have 0. Here in setting of 48G we get last 4 bytes all 0. As for last 0 in binary it represent 0000. So 2 letters of 0 in hexadecimal just form 1 byte(8 bits). So last 8 letters in hexadecimal means we got last 4 bytes 0. And forming this type causes to fire hug.

Solution of the problem

Solution 1)
As bug fires whenever in HEXADECIMAL representation we have 00000000 as the last 4 bytes(8 letters in hex value). So our target is to set SGA_TARGET so that it does not form like this way(4 bytes 0 at last). We can do this if we set our SGA_TARGET not exact multiple of 4G.
Doing set our SGA_TARGET to 47G would happen not the last 4 bytes as all 0s of binary representation.

ALTER SYSTEM SET SGA_TARGET=47G SCOPE=BOTH;
SQL> select to_char(47*1024*1024*1024,'XXXXXXXXXXXXX') hex from dual;
HEX
---------------
BC0000000
We see last 7 letters as 0, and hence the bug will not fire.

Solution 2)
Out database version was 10.2.0.1; base version. And this bug fires. This bug remains in oracle patchset 10.2.0.2 and 10.2.0.3. And is fixed in 10.2.0.4 patchset. So applying patchset 10.2.0.4 would fix the problem.

Solution 3)
Upgrade your database to 11.1.0.6 also will fix the problem.

Solution 4)
Apply the one-off Patch 4433838.

Related Documents
ORA-07445: exception encountered: core dump SIGSEGV
Crash Recovery Fails With ORA-27067

Saturday, October 18, 2008

How to generate fibonacci series in Oracle.

Way 1:


with data as (select level levels from dual
connect by level <= &how_may_rows) 
select f from data 
model dimension by (levels) 
measures ( 0 f) 
rules (   f[1] = 0 , f[2] = 1 , f[levels>2]=f[cv(levels)-2]+f[cv(levels)-1]
);
Enter value for how_may_rows: 10
old   2:                       connect by level <= &how_may_rows)
new   2:                       connect by level <= 10)

F
----------
0
1
1
2
3
5
8
13
21
34

10 rows selected.

Way 2: Just a variant of way 1,

SQL> select s seq from dual
model return all rows
dimension by ( 0 d ) measures ( 0 s )
rules iterate (&n) (
s[iteration_number ] = decode(
iteration_number, 0, 0, 1, 1, s[iteration_number-2]
) + nvl(s[iteration_number-1],0)
)
/
Enter value for n: 8
old   4:     rules iterate (&n) (
new   4:     rules iterate (8) (

SEQ
----------
0
1
1
2
3
5
8
13

8 rows selected.

Way 3: Using Math Formula

SQL> select round ((power ((1 + sqrt (5)) / 2, level - 1) - power ((1 - sqrt (5)) / 2, level - 1)) / sqrt (5)) fib
from dual
connect by level <=&n;
Enter value for n: 8
old   3: connect by level <=&n
new   3: connect by level <=8

FIB
----------
0
1
1
2
3
5
8
13

8 rows selected.

Thursday, October 16, 2008

RMAN Incremental database backup in Oracle

I will demonstrate RMAN incremental database backup in this post.
My requirement is as follows.

1)Database running is noarchivelog mode. As I wish to take backup online so database need to run in archivelog mode.

2)I will use flash recovery area to take backup for better management of backup data.

3)Take full database backup or level 0 incremental backup in a week and each day will take incremental backup cumulative.

4)As a repository I will use recovery catalog in order to store backup information.

5)I need to setup rman configuration like,
-autobackup on(in order to back up the control file and spfile whenever the database structure metadata in the control file changes and whenever a backup record is added) ,

-backup optimization on (in order to skip logs that it has already backed up to the specified device). and,

-recovery window to 7 days. (to ensure that you can recover the database to any point within the last week)

6)My recovery catalog database name is neptune, listener port 1522 and serice name ARJU.NEPTUNE.ARJUBD.COM.

Below is the step by step solution.

Step 01: Connect to source database as sysdba.
oracle@neptune ~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 15 23:22:04 2008

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Step 02: See the log mode of database.
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

Step 03: Shutdown the source database.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 04: Start the database in mount state.
SQL> startup mount
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 109055528 bytes
Database Buffers 117440512 bytes
Redo Buffers 6365184 bytes
Database mounted.

Step 05: Enable archive log.
SQL> alter database archivelog;
Database altered.

Step 06: Open the database.
SQL> alter database open;
Database altered.

Step 07: Check the archived destination and flash recovery area size and location.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 83
Next log sequence to archive 85
Current log sequence 85

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle/app/oracle/product/10.
2.0/db_1/flash_recovery_area
db_recovery_file_dest_size big integer 50G
If you see after giving archive log list Archive destination is not shown as USE_DB_RECOVERY_FILE_DEST then set it by,
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

Step 08: Connect to rman of source database.
SQL> host rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:24:16 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ARJU (DBID=259530685)

Step 09: Change some configuration settings of source database.
RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> configure backup optimization on;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored

Step 10: Connect to the recovery catalog database.
oracle@neptune ~$ sqlplus sys/sistem@neptune:1522/ARJU.NEPTUNE.ARJUBD.COM as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 15 23:35:21 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Step 11: Create recovery catalog user and tablespace and assign permission.
SQL> create user rman identified by rman;

User created.

SQL> create tablespace catalog_spc datafile '/oradata1/catalog.dbf' size 10M autoextend on maxsize unlimited;

Tablespace created.

SQL> alter user rman default tablespace catalog_spc temporary tablespace temp quota unlimited on catalog_spc;

User altered.

SQL> grant recovery_catalog_owner to rman;

Grant succeeded.

Step 12: Connect to recovery catalog database.
bash-3.00$ rman catalog rman/rman@neptune:1522/ARJU.NEPTUNE.ARJUBD.COM

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:39:43 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to recovery catalog database

Step 13: Create recovery catalog schema objects.
RMAN> create catalog tablespace catalog_spc;

recovery catalog created

Step 14: Be sure whether recovery catalog objects successfully created.
SQL> select table_name from tabs;

Step 15: Connect to rman on source and recovery catalog database.
bash-3.00$ rman target / catalog rman/rman@neptune:1522/ARJU.NEPTUNE.ARJUBD.COM

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:41:53 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ARJU (DBID=259530685)
connected to recovery catalog database

Step 16: Register database with recovery catalog.
RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Step 17: Check whether registration was successful.
RMAN> report schema;

If this return database structure then registration is successful.

Step 18: Create scripts for incremental database backups.
This script is for full database backup level 0. This one will be run on weekly basis.
CREATE GLOBAL SCRIPT global_full_backup
{
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
This script is for incremental cumulative database backup level 0. This one will be run on daily basis.
CREATE GLOBAL SCRIPT global_incr_backup
{
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}

This one is for RMAN incrementally updated backups. I just made this and will not use it. For huge database we may consider it.
CREATE GLOBAL SCRIPT global_incr_updated_backup
{
RECOVER COPY OF DATABASE WITH TAG 'inc_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'inc_update'
DATABASE;
}

A sample output after creating global_full_backup,
RMAN> CREATE GLOBAL SCRIPT global_full_backup;
{
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}

starting full resync of recovery catalog
full resync complete
created global script global_full_backup

Step 19: Run these scripts and take backups.
Weekly,
RMAN>RUN{
EXECUTE GLOBAL SCRIPT global_full_backup;
}

Daily,
RMAN>RUN{
EXECUTE GLOBAL SCRIPT global_incr_backup;
}

You may consider also inside script like,
RMAN> backup database plus archivelog delete all input;
in order to delete archived log from all locations that already taken backup.

Or, you may issue,
RMAN> delete archivelog all backed up 1 times to disk;
in order to delete archive log that have at least 1 times backed up to disk.
Related Documents
What will be the Archived Redo Log Destination in Oracle

Archive Destination Settings fails with ORA-32017 and ORA-16179
Backup Database in Noarchivelog mode

how to take backup of oracle database from remote system How to exclude a tablespace from whole database backup User Managed hot backup of oracle database

Tuesday, October 14, 2008

Same sounded words in oracle

Monday, October 13, 2008

Automatic startup and shutdown oracle on linux

Oracle database server provides two scripts to configure automatic database startup and shutdown process.

The scripts are,
$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut

Now let's look at unix level script. When a unix machine boots it runs scripts beginning with Snnname in /etc/rc3.d.

-Here the number nn indicates the order in which these scripts will be run. The name just indicates the function of the script.

In the same way shutdown scripts are named as Knnname which are run from /etc/rc0.d.

If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.

The database script dbstart and dbora will be called from OS script /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.

Note that dbstart and dbshut take each SID, in turn, from the /etc/oratab file and
startup or shutdown the database.

Automate Startup/Shutdown of Oracle Database on Linux
Step 01: Be sure that oratab file is correct and complete.
Check for oratab file either in /etc/oratab or in /var/opt/oracle/oratab.
Database entries in the oratab file have the following format:
$ORACLE_SID:$ORACLE_HOME:[Y|N]
Here Y indicates that the database can be started up and shutdown using dbstart/dbshut script.

If in my database there is two database named arju and arjudup then my oratab file will contain the entry like,
arju:/var/opt/oracle/product/10.2.0/db_1:Y
arjudup:/var/opt/oracle/product/10.2.0/db_1:Y
where /var/opt/oracle/product/10.2.0/db_1 is the $ORACLE_HOME of my database.

Step 02: Create a script to call dbstart and dbshut.
In this example I will create one script that will do both startup and shutdown operation. I will name this script as dbora and will be placed in '/etc/init.d'.

a) Login as root.
b) Change directories to /etc/init.d
c) Create a file called dbora and chmod it to 750.

# touch dbora
# chmod 750 dbora
d)Edit the dbora file and make the contents of it like below.

#!/bin/bash
#
# chkconfig: 35 99 10   
# description: Starts and stops Oracle processes
#
ORA_HOME=/var/opt/oracle/product/10.2.0/db_1
ORA_OWNER=oracle

case "$1" in
'start')

# Start the TNS Listener
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
# Start the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
# Start the Intelligent Agent
if [ -f $ORA_HOME/bin/emctl ];
then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start agent"
elif [ -f $ORA_HOME/bin/agentctl ]; then

su - $ORA_OWNER -c "$ORA_HOME/bin/agentctl start"
else
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl dbsnmp_start"
fi
# Start Management Server
if [ -f $ORA_HOME/bin/emctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
elif [ -f $ORA_HOME/bin/oemctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/oemctl start oms"
fi
# Start HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl]; then
su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl start"
fi
touch /var/lock/subsys/dbora
;;
'stop')
# Stop HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl ]; then

su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl stop"
fi
# Stop the TNS Listener

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
# Stop the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
rm -f /var/lock/subsys/dbora
;;
esac
# End of script dbora

3.As root perform the following to create symbolic links:

# ln -s /etc/init.d/dbora /etc/rc3.d/S99oracle
# ln -s /etc/init.d/dbora /etc/rc0.d/K01oracle

Alternatively you can register the Service using
/sbin/chkconfig --add dbora

This action registers the service to the Linux service mechanism.

4. Test the script to see if it works.

The real test is to reboot unix box and then see whether oracle is started up automatically or not.

However to test the script created in step 2, without rebooting, do the following:

Login as root and then,
# /etc/init.d/dbora start (for startup)
# /etc/init.d/dbora stop (for shutdown)

If you restart start and stop oracle database is successful then you are almost done.

Related Documents
How to Find out or Check Linux Version Information
How to change the hostname in Linux
How to Identify OS or Oracle 64 bit or 32 bit on Unix

ORA-28547 connection to server failed, probable Net8 admin error

Error Description
This problem occurs on windows machine. Whenever you login as a user of windows domain group then in the database all connections both local and remote, including SYSDBA, fail with an ORA-28547.

ORA-28547 connection to server failed, probable Net8 admin error

Cause of the Problem
Oracle Native Authentication Services is failing.

Solution of the Problem
Way 1:
Disable Oracle Native Authentication Services. In the server machine inside sqlnet.ora change the parameter value of SQLNET.AUTHENTICATION_SERVICES from (NTS) to (NONE).

Way 2:
Change the database service to run as a user who is a member of the Domain Admin group. Because in order to connect using native authentication service oracle Log on user must be a privileged user to allow it to correctly authenticate the client user for Native Authentication.

To do so,
1.Go to Services. Right click on My computer>Select Manage>Select Services and Application> Select Services> On the right side From a list Scroll to your OracleService$ORACLE_SID
You will search for your database name instead of $ORACLE_SID of OracleService$ORACLE_SID.

2. Right click on the service and click the properties of the database service, select the LogOn tab.

3. Select "This Account" and add you domain admin account details and Ok

4. Shutdown the database

5. Stop and restart the service

Backup to remote location fails with ORA-19504 andORA-27054

Error Description
I mounted remote location jupiter:/export/home/oracle on my local server as /export/home/oracle/remote and then I wanted to take RMAN backup on the mapped drive but it fails with ORA-19504: and ORA-27054:.
RMAN> backup format '/export/home/oracle/remote/%U' datafile 4;

Starting backup at 13-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oradata1/arju/ARJU/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-OCT-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/13/2008 04:02:40
ORA-19504: failed to create file "/export/home/oracle/remote/03jt0tav_1_1"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2
Cause of the Problem
From Oracle 10.2g , Oracle checks by which options NFS mount is mounted on local filesystem. This checking is done to ensure that no corruption of the database can happen as incorrectly mounted NFS volumes can result in data corruption.

Generally the following checks are done on Solaris and AIX systems.

1) In order to check the mount option whether mount table can be read. Issue,
# cat /etc/mnttab

2) While NFS mount is "hard" option provided.
3) Whether the mount options include rsize>=32768 and wsize>=32768
4) If RAC environments, where NFS disks are supported, whether the "noac" mount option is used.

If above requirements are full and still above error appear then possibly oracle bug.
Solution of the Problem
Solution 1: Setting the mount option explicitly.
# mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 jupiter:/export/home/oracle /export/home/oracle/remote

The default rsize and wsize for NFS is 4096 Blocks

So if you have rsize=32k and wsize=32k then NFS would be able to read and write large datagram as
compared to deafult one.

TCP option will make sure that your client are getting the data or not

Hard & INTR - The program accessing a file on a NFS mounted file system will hang when the
server crashes. The process cannot be interrupted or killed unless you also specify intr. When
the NFS server is back online the program will continue undisturbed from where it was.

actimeo is for access timeout and it should be 0 .

After setting above parameters in NFS mount clause you will be able to take the backup
RMAN> backup format '/export/home/oracle/remote/%U' datafile 4;
And see it would be successful.

Solution 2: Set the Event 10298
SQL> alter system set event="10298 trace name context forever, level 32" scope=spfile;
System altered.

Now restart server and take backup.
SQL> startup force
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 125832744 bytes
Database Buffers 100663296 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.

SQL> show parameter event

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string 10298 trace name context forev
er, level 32

RMAN> backup format '/export/home/oracle/remote/%U' datafile 4;

Starting backup at 13-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oradata2/arjudba/arjudba/arju/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-OCT-08
channel ORA_DISK_1: finished piece 1 at 13-OCT-08
piece handle=/export/home/oracle/remote/08jt0voi_1_1 tag=TAG20081013T044402 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-OCT-08

Solution 3: If Oracle bug
If still fails then possibly an oracle bug for Oracle 10.2.0.1. In order to solve bug apply 5146667 one off patch for 10.2.0.1.

Related Documents
How to take RMAN backup on a remote disk

How to share and Access file on Solaris machine

In this example I will share a file from jupiter machine and then access it on neptune machine. Both machine uses Solaris platform.
1)On jupiter machine,

bash-3.00$ hostname
jupiter

Log on as a root user,
bash-3.00$ su
Password:

Change shell to bash
# bash

To make share persistence (after reboot is will also show) edit /etc/dfs/dfstab entry. Here I want to share directory /export/home/oracle and sharing option is read write.
# vi /etc/dfs/dfstab
share -F nfs -o rw /export/home/oracle

rw means read write permission You can also want to give ro (read only) instead of read write.

If you want it temporary you can do,
# share -F nfs -o rw /export/home/oracle
Restart your nfs server.
# /etc/init.d/nfs.server stop
# /etc/init.d/nfs.server start

Have a look at whether nfs server is running or not.
# ps -ef | grep nfs
daemon 317 1 0 Oct 10 ? 0:00 /usr/lib/nfs/lockd
daemon 311 1 0 Oct 10 ? 0:00 /usr/lib/nfs/statd
daemon 313 1 0 Oct 10 ? 0:03 /usr/lib/nfs/nfsmapid
daemon 291 1 0 Oct 10 ? 0:00 /usr/lib/nfs/nfs4cbd
root 12741 12424 0 03:20:34 pts/4 0:00 grep nfs

This must show statd and lockd running. In fact The statd and lockd must be running on server and
client.

Have a look at which file is shared and it's mode.
# share
- /export/home/oracle rw ""

2)On Saturn machine log on as a root user
oracle@neptune ~$ su -
Password:

Create a directory where you mount the network location.
root@neptune /# mkdir /export/home/oracle/remote

Mount the network drive
root@neptune /# mount -F nfs jupiter:/export/home/oracle /export/home/oracle/remote

Or, with more options issue,
# mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 jupiter:/export/home/oracle /export/home/oracle/remote
See whether it is mounted.
root@neptune /# df -h
.
.

jupiter:/export/home/oracle
20G 7.6G 12G 39% /export/home/oracle/remote

Now do work is it /export/home/oracle/remote is your local disk location.
Related Documents
Memory Usage of Solaris Operating System

Sunday, October 12, 2008

How to take RMAN backup on a remote disk/ location

It is common to hear that for backup there is not sufficient space on my server or my server disk is about to full. And now I want to take my RMAN backup directly to remote location. How I can achieve that?

Now before taking backup let think in which type of storage on remote location you want to take backup? Is it a disk or to the tape. Below is the procedure for both types.

Taking Remote RMAN backup to Tape
1)This is of no different as you access remote tape devices.

2)First install and configures the drivers of the tape device on the local machine box.

3)Make sure that you can access to tape by making copies file to tape.

4)If step3 is successful then RMAN will be able to take backup to tape directly.

Taking Remote RMAN backup to disk
1)Remember that direct disk backup to remote location using RMAN is not possible as we can do it on local disk by using FORMAT parameter specifying local disk.

2)As RMAN backups to DISK can only be taken to some 'part' of the target database server. So, remote location will not work.

3)The thing you can achieve goal is to share remote location, then mount it on the target database where you want to take backup. An example of how to share folder and access it on other machine is discussed on How to share and Access file on Solaris Machine

4)Take RMAN backup to this mapped location using FORMAT clause. Based on this a remote backup is taken in http://arjudba.blogspot.com/2008/10/backup-to-remote-location-fails-with.html after sharing remote location and map it to location /export/home/oracle/remote/.
Related Documents
RMAN Backup format specification
Set up a Database for RMAN Backup
Create Multiple Duplex backup by one Backup Command

New features of oracle perfomance in 10.2g

In oracle database 10.2g release a lots of performance feature are added in it.

1)Active Session History Reports (ASH): ASH can be used to identify blocking session and waiting session and associated transaction identifiers and SQL for a specified duration.

2)Automatic PGA Memory Management: PGA memory can be managed dynamically and new view V$PROCESS_MEMORY has been added.

3)Automatic Shared Memory Management: Many of the SGA memory components can be dynamically sized by using automatic memory management.

4)Automatic Tuning of Multiblock Read Count: The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly.

5)Automatic Workload Repository(AWR)Reports: Various AWR reports help us to get statistics between two snapshot id.

6)Automatic Workload Repository SQL Collection can be configured: The AWR collects, process and maintain statistics of Top SQLs. Moreover the collection criteria can be configured.

7)Database Replay: With this feature database workload of a production database can be captured and then it can be replayed/tested on a test system as the same way as recorded was in production system with the same timing, concurrency, and transaction dependencies of the production system. On 10.2g only workload can be captured and it can be replayed on 11.1g.

8)End to End Application Tracing: End to end application tracing diagnosis performance problem in a multitier environment. With this feature you can identify the source of an excessive workload, such as a high load SQL statement, and allow you to contact the specific user responsible.

9)Improved System Statistics: The V$SYSSTAT view has added rows to capture the total number of physical I/O's performed by any Oracle process.

10)SQL Access Advisor: The DBMS_ADVISOR package and SQL Access Adviser advised different recommendation based on query.

11)SQL Performance Analyzer: It enables you to forecast the impact of system changes on SQL performance .

12)SQL Profiles:

13)SQL Tuning Advisor:

14)SQL Tuning Sets: With DBMS_SQLTUNE package SQL Tuning sets can be export and imported to another system.

15)V$SQLSTATS View: V$SQLSTATS returns performance statistics.

Related Documents

Improving Index creation speed in Oracle
New features of oracle perfomance in 10.2g
How to find Top Sql through Queries
Difference or Advantage between AWR and STATSPACK report
Ways of Identify High Load ot top SQL
Oracle INSERT Performance
Optimize Data Access Path in Oracle
When you would make Index and when not.