World of Oracle DBA

Problem Description While doing online redefinition on a table dbms_redefinition.copy_table_dependents fails with error ORA-23540 as below. SQL> set serverout on SQL> declare 2 error_count pls_integer := 0; 3 BEGIN 4 dbms_redefinition.copy_table_dependents('MUCI', 'HISTORY', 'HISTORY_',1, true, true, true, false,error_count); 5 dbms_output.put_line('errors := ' || to_char(error_count)); 6 END; 7 / declare * ERROR at line 1: ORA-23540: Redefinition not defined or initiated ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1662 ORA-06512: at line 4 Cause of the problem There may be different causes against it. If the schema specified in the dbms_redefinition.copy_table_dependents procedure does not exist already in the database then above error can appears. Suppose it may be the case that you specified schema name as lower case but by default username is created in uppercase. There may b...

You sometimes may wish to access to logon to your database filtered by IP address. Suppose you will allow to connect to database having a list of IP address. Or you like to ban a list of IP addresses in order to deny logon as a database user. With oracle this scenario can be achieved, however this seems to me a bit of fun. The secret lies in the SQLNET.ORA file. On UNIX system this file resides in $ORACLE_HOME/network/admin directory along with tnsnames.ora and listener.ora. In order to put any filtering by IP address open the sqlnet.ora file with any editor and insert the following line, tcp.validnode_checking = yes This in fact, turns on the hostname/IP checking for your listeners. After this, with tcp.invited_nodes /tcp.excluded_nodes you can supply lists of nodes to enable/disable, as such: tcp.invited_nodes = (hostname1, hostname2) tcp.excluded_nodes = (192.168.100.101,192.168.100.160) Note that if you only specify invited nodes with tcp.invited_nodes, all other nodes will be excl...

We will do partition of table OUT_CDR which reside on CR_2 schema. We will do partition on column CDATE using RANGE partitioning technique. We will not change any table structure other than partition. All indexes, constraints, triggers, privileges defined on the table will be remain same. Step 01: Let's have a look at the table on which we will do partitioning. SQL> set pagesize 200 SQL> set long 999999 SQL> set linesize 150 SQL> select dbms_metadata.get_ddl('TABLE','OUT_CDR','CR_2') from dual; DBMS_METADATA.GET_DDL('TABLE','OUT_CDR','CR_2') -------------------------------------------------------------------------------- CREATE TABLE "CR_2"."OUT_CDR" ( "ID" NUMBER(32,0) NOT NULL ENABLE, "CDATE" DATE NOT NULL ENABLE, "DDATE" DATE NOT NULL ENABLE, "ACCTSESSIONID" VARCHAR2(100), "CALLINGNO" VARCHAR2(100), "CALLEDNO" VA...

Error Description While connecting to database user get the error, ORA-01033: ORACLE initialization or shutdown in progress Cause of The Problem SYSDBA used issued STARTUP command to the database and while starting up database, connecting to database as normal user will get the error ORA-01033. There may be the scenario that SHUTDOWN command waits a long time. Solution of The Problem Scenario 01: Based on the situation STARTUP may take fewer minutes. As a normal user what more you do is to wait few minutes and try again. You will succeed if database is open state. Here is the waiting result. C:\Documents and Settings\Queen>sqlplus shaik/a SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 7 17:34:23 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. ERROR: ORA-01033: ORACLE initialization or shutdown in progress Enter user-name: shaik Enter password: ERROR: ORA-01033: ORACLE initialization or shutdown in progress Enter user-name: shaik Enter password: Connected to: Oracl...

Problem Description While I attempt to reorganize a table online whenever I do with DBMS_REDEFINITION.can_redef_table or DBMS_REDEFINITION.START_REDEF_TABLE procedure on the table it fails with error ORA-12091 as below. SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR'); BEGIN dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR'); END; * ERROR at line 1: ORA-12091: cannot online redefine table "CR_2"."OUT_CDR" with materialized views ORA-06512: at "SYS.DBMS_REDEFINITION", line 137 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478 ORA-06512: at line 1 Cause of the Problem If you unsuccessfully launched the DBMS_REDEFINITION.START_REDEF_TABLE procedure once, the materialized view would be created on the interim table and still be remained there. So subsequent run of the DBMS_REDEFINITION.CAN_REDEF_TABLE BMS_REDEFINITION.START_REDEF_TABLE will return error until you remove the materalized view against the...

Problem Description My impdp operation fails with error ORA-39002, ORA-39070, ORA-29283, ORA-29283: invalid file operation as below. SQL> host impdp cr_3/cr_3 remap_schema=cr_2:cr_3 dumpfile=skydbcr_2-04-10-2008.dmp directory=e Import: Release 10.2.0.1.0 - Production on Wednesday, 12 November, 2008 16:42:27 Copyright (c) 2003, 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 ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 475 ORA-29283: invalid file operation Cause and Solution of the Problem 1. Check whether the user who run the expdp operation has READ,WRITE permission on the database directory. If not then as a dba user run grant permission on cr_3, GRANT READ,WRITE ON DIRECTORY E TO CR_3; 2. Check whether the path used in database directory physically exists ...

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/shiak_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 se...

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 v_result := to_char(decimal_num); 11 elsif (decimal_num 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 ...

Problem Symptom While performing a simple select statement it fails with error ORA-00923: FROM keyword not found where expected like below. SQL> select 48*1024*1024*1024 decimal from dual; select 48*1024*1024*1024 decimal from dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected Cause of the problem The ORA-00923 can be caused by two different reasons. 1)Oracle itself expect FROM keyword in the position but it could not find any. Like below, SQL> select 48*1024 om dual; select 48*1024 om dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected As no from keyword found so error arises. Oracle expected from in place of keyword dual. 2)The secong cause can sometimes mislead you. Like in case of SQL> select 48*1024*1024*1024 decimal from dual; select 48*1024*1024*1024 decimal from dual * ERROR at line 1: ORA-00923: FROM keyword not found where exp...

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 Cluster...

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 part...

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 com...

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); I...

Error Description When using DBMS_ADVISOR on a table in SYS or SYSTEM schema, the following errors returned. ORA-13600: error encountered in Advisor QSM-00794: the statement can not be stored due to a violation of the invalid table reference filter Below is an example. SQL> conn system/s Connected. SQL> BEGIN DBMS_ADVISOR.quick_tune( advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name => 'emp_quick_tune', attr1 => 'SELECT * FROM emp WHERE EMPNO = 7788'); END; / BEGIN * ERROR at line 1: ORA-13600: error encountered in Advisor QSM-00775: the specified SQL statement cannot be stored in the workload due to invalid table references ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 1501 ORA-06512: at "SYS.WRI$_ADV_SQLACCESS_ADV", line 176 ORA-06512: at "SYS.PRVT_ADVISOR", line 2594 ORA-06512: at "SYS.DBMS_ADVISOR", line 726 ORA-06512: at line 2 SQL> create table emp(empno number); Table created. SQL> BEGIN DBMS_AD...

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 ? ...

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 config...

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/shaik/SHAIK/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: faile...

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 Startu...

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 Scro...

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 1...

Way 1: with data as (select level levels from dual connect by level 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 new 2: connect by level 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 ...

Whenever I try to connect remotely to a database through rman it fails with message RMAN-00554, RMAN-04005, ORA-0103. In this example my source database is saturn and destination database is jupiter. From jupiter let me try to connect to saturn. Source database Before proceed let's see the parameter that we can use to connect to rman without tnsnames.ora. Our needed parameter is hostname/ipaddress, service_name and port number. These three can be found by issuing lsnrctl status in source machine. Note that you must have a password file setup in the source database. This requires the use of the "orapwd" command and the initialization parameter "REMOTE_LOGIN_PASSWORDFILE". bash-3.00$ hostname saturn SQL> show parameter remote_login NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE bash-3.00$ lsnrctl status LSNRCTL for S...

Automatic statistics gathering should be sufficient for most of the cases. And automatic statistics gathering is done by default during an overnight batch window. In most cases automatic statistics gather is sufficient but still you may need to gather manually statistics if you do huge insert or update or delete on the table. You might know we can gather statistics by two ways. 1)Analyze command. 2)DBMS_STATS package. By querying from the dba_tables view you can say by which method you gathered statistics. Let's see it with an example. I am creating two tables named with_analyze and with_dbms_stats. On table with_analyze use analyze to estimate statistics and on table with_dbms_stats use DBMS_STATS package to gather statistics. SQL> Create table with_analyze(col1 number); Table created. SQL> Create table with_dbms_stats(col1 number); Table created. SQL> analyze table with_analyze compute statistics; Table analyzed. SQL> exec dbms_stats.gather_table_stats('SYS',...