The Systems People Inc.

The Systems People Inc.

Customer Focused Service since 1993

Recovery from loss of Temp File(s) in Temporary Tablespace

In Oracle Temporary Tablespace is used for sort / merge operations that cannot be done within memory. When some datafile for Temporary Tablespace (Tempspace) is lost or inaccessible, database will perform normally, however certain user queries that require the use of Tempspace will return error.

 

In 11g Oracle can re-create missing TEMP files at the start of instance. Also you can manually create the missing / damaged Temp Files. This entry describes the steps for the same.

Set up for testing recovery from loss of temp files…

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> col name format a60;
SQL> select file#, bytes, blocks, name from v$tempfile;
     FILE#      BYTES     BLOCKS NAME
---------- ---------- ---------- -----------------------------------------------
         1  105906176      12928 /u02/oradata/orcl/temp01.dbf

SQL>

Let us quickly check the sort area allocated in memory in our instance

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     65536
SQL>

We’ll reduce it to small size so that SORT operation will be forced to go to disk.

SQL> alter session set sort_area_size=50;

Session altered.

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     50
SQL>

Now we’ll create a table and issue query with Order By clause to force the use of Tempspace.

SQL> set autotrace off;
SQL> create table t1 as select * from all_objects;

Table created.

SQL> insert into t1 select * from all_objects;

68938 rows created.

SQL> /

68938 rows created.

… REPEAT REPEAT …

SQL> /
68938 rows created.

SQL> commit;
Commit complete.

SQL> select count(*) from t1;
COUNT(*)
   ----------
    620442

SQL>
SQL> set autotrace traceonly statistics;
SQL> select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc;

620442 rows selected.

Statistics
----------------------------------------------------------
         77  recursive calls
          4  db block gets
       9179  consistent gets
      12609  physical reads
          0  redo size
   11896316  bytes sent via SQL*Net to client
     455402  bytes received via SQL*Net from client
      41364  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
     620442  rows processed

SQL>

Now we have a query, which ensures the use of Tempspace;

We’ll remove the temp file at OS level, and reissue the query. We’ll get an error.

Automatic re-creation (recovery) of TEMP datafiles on instance restart

SQL> select name from v$tempfile;
NAME
------------------------------------------------------------
/u02/oradata/orcl/temp01.dbf

SQL> !rm /u02/oradata/orcl/temp01.dbf;

SQL> !ls /u02/oradata/orcl/temp01.dbf;
ls: /u02/oradata/orcl/temp01.dbf: No such file or directory

Note: In UNIX/Linux environment the deletion of file is NOT reflected immediately or rerunning the same query may use the DB Cache and the rerun of the query may still continue successfully.

SQL>  select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc,6,7,8;
 select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc,6,7,8
               *
ERROR at line 1:
ORA-01565: error in identifying file '/u02/oradata/orcl/temp01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL>

In Oracle 11g the instance startup will re-create the Temporary datafiles. If require they can be created manually as well.Since this is a test instance, I am going to restart the instance to see re-creation of Tempfiles.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> !ls /u02/oradata/orcl/temp01.dbf;
/u02/oradata/orcl/temp01.dbf

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     65536
SQL> alter session set sort_area_size=50;

Session altered.

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     50
SQL>
SQL>  set autotrace traceonly statistics;
ERROR:
ORA-24315: illegal attribute type


SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report
SQL> exit;

I noticed, after restart of the database, the existing SQLPlus session had some problem in setting autotrace. However exiting and restarting SQLPlus session resolved this issue.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-00-7C-94:[/home/oracle]
$ sqlplus  / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 14 11:41:03 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     65536
SQL> alter session set sort_area_size=50;

Session altered.

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     50
SQL> set autotrace traceonly statistics;
SQL>

Note that upon restart of the instance the temporary Datafile was recreated.

SQL>  !ls /u02/oradata/orcl/temp01.dbf;
/u02/oradata/orcl/temp01.dbf

SQL> select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc,6,7,8;

827256 rows selected.


Statistics
----------------------------------------------------------
       1549  recursive calls
        441  db block gets
      12452  consistent gets
      24801  physical reads
          0  redo size
   14888276  bytes sent via SQL*Net to client
     607070  bytes received via SQL*Net from client
      55152  SQL*Net roundtrips to/from client
         37  sorts (memory)
          1  sorts (disk)
     827256  rows processed

SQL>

Note that SORT operations is successful with AUTO created TEMP FIles on instance restart.

Next post I'll demonstrate the manually creating Temp Files when the Database is Open.

Till then BYE!

Recovery from loss of Temp File(s) in Temporary Tablespace

In Oracle Temporary Tablespace is used for sort / merge operations that cannot be done within memory. When some datafile for Temporary Tablespace (Tempspace) is lost or inaccessible, database will perform normally, however certain user queries that require the use of Tempspace will return error.

 

In 11g Oracle can re-create missing TEMP files at the start of instance. Also you can manually create the missing / damaged Temp Files. This entry describes the steps for the same.

Set up for testing recovery from loss of temp files…

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> col name format a60;
SQL> select file#, bytes, blocks, name from v$tempfile;
     FILE#      BYTES     BLOCKS NAME
---------- ---------- ---------- -----------------------------------------------
         1  105906176      12928 /u02/oradata/orcl/temp01.dbf

SQL>

Let us quickly check the sort area allocated in memory in our instance

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     65536
SQL>

We’ll reduce it to small size so that SORT operation will be forced to go to disk.

SQL> alter session set sort_area_size=50;

Session altered.

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     50
SQL>

Now we’ll create a table and issue query with Order By clause to force the use of Tempspace.

SQL> set autotrace off;
SQL> create table t1 as select * from all_objects;

Table created.

SQL> insert into t1 select * from all_objects;

68938 rows created.

SQL> /

68938 rows created.

… REPEAT REPEAT …

 

SQL> /

 

68938 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> select count(*) from t1;

 

  COUNT(*)

----------

    620442

 

SQL>

SQL> set autotrace traceonly statistics;

SQL> select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc;

 

620442 rows selected.

 

Statistics

----------------------------------------------------------

         77  recursive calls

          4  db block gets

       9179  consistent gets

      12609  physical reads

          0  redo size

   11896316  bytes sent via SQL*Net to client

     455402  bytes received via SQL*Net from client

      41364  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

     620442  rows processed

 

SQL>

Now we have a query, which ensures the use of Tempspace;

We’ll remove the temp file at OS level, and reissue the query. We’ll get an error.

Automatic re-creation (recovery) of TEMP datafiles on instance restart

SQL> select name from v$tempfile;
NAME
------------------------------------------------------------
/u02/oradata/orcl/temp01.dbf

SQL> !rm /u02/oradata/orcl/temp01.dbf;

SQL> !ls /u02/oradata/orcl/temp01.dbf;
ls: /u02/oradata/orcl/temp01.dbf: No such file or directory

Note: In UNIX/Linux environment the deletion of file is NOT reflected immediately or rerunning the same query may use the DB Cache and the rerun of the query may still continue successfully.

SQL>  select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc,6,7,8;
 select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc,6,7,8
               *
ERROR at line 1:
ORA-01565: error in identifying file '/u02/oradata/orcl/temp01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL>

In Oracle 11g the instance startup will re-create the Temporary datafiles. If require they can be created manually as well.Since this is a test instance, I am going to restart the instance to see re-creation of Tempfiles.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> !ls /u02/oradata/orcl/temp01.dbf;
/u02/oradata/orcl/temp01.dbf

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     65536
SQL> alter session set sort_area_size=50;

Session altered.

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     50
SQL>
SQL>  set autotrace traceonly statistics;
ERROR:
ORA-24315: illegal attribute type


SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report
SQL> exit;

I noticed, after restart of the database, the existing SQLPlus session had some problem in setting autotrace. However exiting and restarting SQLPlus session resolved this issue.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-00-7C-94:[/home/oracle]
$ sqlplus  / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 14 11:41:03 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     65536
SQL> alter session set sort_area_size=50;

Session altered.

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size              integer     0
sort_area_size                       integer     50
SQL> set autotrace traceonly statistics;
SQL>

Note that upon restart of the instance the temporary Datafile was recreated.

SQL>  !ls /u02/oradata/orcl/temp01.dbf;
/u02/oradata/orcl/temp01.dbf

SQL> select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc,6,7,8;

827256 rows selected.


Statistics
----------------------------------------------------------
       1549  recursive calls
        441  db block gets
      12452  consistent gets
      24801  physical reads
          0  redo size
   14888276  bytes sent via SQL*Net to client
     607070  bytes received via SQL*Net from client
      55152  SQL*Net roundtrips to/from client
         37  sorts (memory)
          1  sorts (disk)
     827256  rows processed

SQL>

Note that SORT operations is successful with AUTO created TEMP FIles on instance restart.

Next post I'll demonstrate the manually creating Temp Files when the Database is Open.

Till then BYE!

ORA-04068: existing state of packages has been discarded

Exploring ORA-04068 and ORA-04065
 

Since Oracle version 7.3 (probably even before that) ORA-04068 was encountered by developers and database administrators in PL/SQL packages. In this document I’ll attempt to de-mystify this error by exploring what the error means, its purpose and various causes and potential solutions.

One of the main reasons for mystery and frustration with this error is the fact that the package encountering the error can be very different from the object that actually caused the error. The package encountering ORA-04068 is dependent on the object causing the error. This dependency can be immediate (if we are fortunate) or could be arbitrarily long list of database objects. If we are really unfortunate the dependency can be also remote.
Fortunately, this error will resolve itself, upon re-invocation of the package that encountered the error.
There are several potential solutions/workarounds for this error available on the internet websites and blogs. Some of these recommendations are incorrect or dangerous.
As the new adage goes: Be careful of what you learn from Internet ;-)

Introduction to the error ORA-04068

Typically ORA-04068 is followed by ORA-04065 and ORA-06508, as shown below.
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "pkg_c"
ORA-06508: PL/SQL: could not find program unit being called: "pkg_c"
This error is raised by PL/SQL engine upon on invocation of a package which maintains state and whose existing state is destroyed by some action. In such situation the PL/SQL engine is obligated to inform the client (invoker of the package) by raising exception ORA-04068.
Package State Maintenance
A PL/SQL package once invoked in a session will be resident (in the memory) through the life of the session. Specifically if the package has package level variables defined. The package level variables can be in the SPEC or in the BODY. The package will retain the values assigned to these variables for the life of the session.
When a package, say P, invoked in a session (say session 1) is maintaining state and any object that package P depends on is modified, compiled, then the next invocation of package P in session 1 will raise ORA-04068.

DEMO of ORA-04068

In this section, I’ll walk though the creation and some apparent solutions to this error. For simplicity, I will not address remote package dependencies in this document. (May be separate document)

Setup the environment for walk-though

For this section of DEMO, I’ll use a table MYT and two dependent packages. I’ll use only package specifications (and not bodies) for keeping the examples small. Package PKG_P depends on package PKG_C which in turn depends on table MYT.
Session A>drop table myt;
Table dropped.

Session A>drop package pkg_c;
Package dropped.

Session A>drop package pkg_p;
Package dropped.

Session A>create table myt (col1 int);
Table created.

Session A>CREATE OR REPLACE PACKAGE pkg_c
2 AS
3 TYPE t1 IS ARRAY (3) OF NUMBER;
4 rc myt%rowtype; ---- <<<< Dependent on table MYT
5 x1 NUMBER;
6 y1 NUMBER;
7 END;
8 /

Package created.

Session A>CREATE OR REPLACE PACKAGE pkg_p
2 AS
3 x NUMBER;
4 y NUMBER;
5 j pkg_c.t1; ---- <<<< Dependent on package PKG_C
6 END;
7 /
Package created.

Session A>exec pkg_P.Y := 2;

PL/SQL procedure successfully completed.
Session A>

Scenario 1: Package PKG_C recompiled

Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.

Session A>alter package pkg_c compile;
Package altered.

Session A>exec pkg_P.Y := 2;
BEGIN pkg_P.Y := 2; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "PKG_P" has been invalidated
ORA-04065: not executed, altered or dropped package "PKG_P"
ORA-06508: PL/SQL: could not find program unit being called: "PKG_P"
ORA-06512: at line 1

Session A>exec pkg_P.Y := 3;
PL/SQL procedure successfully completed.

Session A>
Subsequent execution of the package in same session does not raise this error, as the state of the package has been reset.
 
When PKG_C is recompiled, Oracle invalidates all dependencies. Subsequent invocation of PKG_P results in ORA-06508 and auto-compile. This recompilation results in package state of PKG_P being discarded, resulting in ORA-04068.

Scenario 2: Package PKG_P recompiled

However recompilation of package PKG_P does NOT result in error in the same session. I believe after compilation of the package, Oracle resets state of the package in that session.
Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.

Session A>alter package pkg_p compile;
Package altered.

Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.

Session A>exec pkg_P.Y := 3;
PL/SQL procedure successfully completed.

Session A>

 

Scenario 3: Recreate or Alter the Table MYT


Similar to Scenario 1, described above, all the dependent packages on the object being altered are invalidated. Next invocation will result in auto-compile and if there is an existing state of the package, then it will result in ORA-04068.

DROP and CREATE

Session A>
exec pkg_P.Y := 2; 
PL/SQL procedure successfully completed. 

Session A>drop table myt; 
Table dropped. 

Session A>create table myt (col1 int); 
Table created. 

Session A>exec pkg_P.Y := 2; 
BEGIN pkg_P.Y := 2; END; 
* 

ERROR at line 1: 
ORA-04068: existing state of packages has been discarded 
ORA-04061: existing state of package “PKG_P” has been invalidated 
ORA-04065: not executed, altered or dropped package “PKG_P” 
ORA-06508: PL/SQL: could not find program unit being called: “PKG_P” 
ORA-06512: at line 1 

Session A>exec pkg_P.Y := 3; 
PL/SQL procedure successfully completed. 

Session A> 
ALTER Table
Session A>exec pkg_P.Y := 2; 
PL/SQL procedure successfully completed. 

Session A>alter table myt add (col2 int); 
Table altered. 

Session A>exec pkg_P.Y := 2; 
BEGIN pkg_P.Y := 2; END; 
* 
ERROR at line 1: 

ORA-04068: existing state of packages has been discarded 
ORA-04061: existing state of package "PKG_P" has been invalidated 
ORA-04065: not executed, altered or dropped package "PKG_P" 
ORA-06508: PL/SQL: could not find program unit being called: "PKG_P" 
ORA-06512: at line 1 

Session A>exec pkg_P.Y := 3; 
PL/SQL procedure successfully completed. 

Session A> 
For a package to maintain state, it must have package level variables declared.
 
Other connected sessions that may have invoked the package will also encounter this error on first invocation, after the package is recompiled.

Scenario 4: Package with package level constants versus variables

In this demonstration, I’ll use two different sessions.

Potential Solutions

I’ll describe some of the solutions and workarounds in future posts.

PRAGMA SERIALLY_REUSABLE

The PRAGMA directive SERIALLY_REUSABLE will definitely eliminate this error from occurring.
HOWEVER, this approach undermines the the objective of the package level variables.

RMAN report/list in Oracle

In our previous post we configured RMAN to disk base backup and cleared SBT.

Let's run some report and list commands to see the status of backups. Verify the FRA configuration.

RMAN> list backup;


RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1    0     /u02/oradata/orcl/system01.dbf
2    0     /u02/oradata/orcl/sysaux01.dbf
3    0     /u02/oradata/orcl/undotbs01.dbf
4    0     /u02/oradata/orcl/users01.dbf
5    0     /u02/oradata/orcl/example01.dbf

RMAN>

There no backups yet for our database. We'll create some backups after urning on the backup optimization.

RMAN> configure backup optimization on;

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

RMAN>

From the output of list archive log we can see that logs are being archived to FRA.

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
1       1    3       A 07-JUN-12
        Name: /u02/flash_recovery_area/ORCL/archivelog/2012_06_07/o1_mf_1_3_7x2q7cyp_.arc

2       1    4       A 07-JUN-12
        Name: /u02/flash_recovery_area/ORCL/archivelog/2012_06_08/o1_mf_1_4_7x34qzvx_.arc

3       1    5       A 08-JUN-12
        Name: /u02/flash_recovery_area/ORCL/archivelog/2012_06_08/o1_mf_1_5_7x3z3p7x_.arc

4       1    6       A 08-JUN-12
        Name: /u02/flash_recovery_area/ORCL/archivelog/2012_06_08/o1_mf_1_6_7x4xh6c6_.arc

5       1    7       A 08-JUN-12
        Name: /u02/flash_recovery_area/ORCL/archivelog/2012_06_08/o1_mf_1_7_7x5dlngn_.arc

6       1    8       A 08-JUN-12
        Name: /u02/flash_recovery_area/ORCL/archivelog/2012_06_09/o1_mf_1_8_7x5rm6jw_.arc

7       1    9       A 09-JUN-12
        Name: /u02/flash_recovery_area/ORCL/archivelog/2012_06_09/o1_mf_1_9_7x6c78bg_.arc


RMAN>

 

Some RMAN reporting in Oracle on AWS

Initiated an Oracle instance on AWS. In this posts I'll review some of the reporting and backup/recover using RMAN.

Log into to the instance (AWS/Oracle) using password. Start RMAN and let's investigate.

login as: oracle
oracle@ec2-23-22-205-159.compute-1.amazonaws.com's password:
oracle@domU-12-31-39-00-7C-94:[/home/oracle]
$
oracle@domU-12-31-39-00-7C-94:[/home/oracle]
$
oracle@domU-12-31-39-00-7C-94:[/home/oracle]
$ rman target=orcl

Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jun 8 18:01:12 2012

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

target database Password:
connected to target database: ORCL (DBID=1313137175)

RMAN> list backup;

using target database control file instead of recovery catalog

RMAN> 

Now, we are in RMAN and realize there are no backups.

Let's check of the database's configuration like, archivelog mode, FRA configureation etc...

$ sqlplus /  as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Jun 8 18:01:43 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user;
USER is "SYS"
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
SQL>

Well the database is in archivelog mode; Let's check the RMAN configuration...

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/u02/admin/orcl/osbws/libosbws11.so';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.1.0/db_1/dbs/snapcf_orcl.f'; # default

RMAN>

This instance is designed for OSB (Oracle Secure Backup) with SBT as default, with channel defined for SBT. We'll change the default to disk.

RMAN> configure default device type to disk;

old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

RMAN>

Now we'll CLEAR the tape related configurations...

RMAN> configure default device type to disk;

old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

RMAN>

Notice with the TAPE CLEAR... all values are set to #default

RMAN> CONFIGURE DEVICE TYPE SBT_TAPE clear;

RMAN configuration parameters are successfully reset to default value

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/u02/admin/orcl/osbws/libosbws11.so';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.1.0/db_1/dbs/snapcf_orcl.f'; # default

RMAN>

Now we are set to default (for SBT) and configured RMAN for disk backup.

 

In next post we'll expolre additional RAN backup reporting.

Log into AWS instance using password authentication

By default SSHD in linux instances in AWS are started with password authentication set to NO. For good resaons. Clear text password authentication is NOT as secured.

However for development instances or sandboxes, it might provide a "convenience" that may be worth.

To enable password authentication to AWS linux instances, modify parameter PasswordAuthentication  parameter in file sshd_config and then restart the sshd process.

For example...

root@domU-12-31-39-00-7C-94:[/etc/ssh]
$ pwd
/etc/ssh
root@domU-12-31-39-00-7C-94:[/etc/ssh]
$
$ vi sshd_config

In the VI editor...

# To disable tunneled clear text passwords, change to no here!
PasswordAuthentication yes # <<<< we enabled this!!!!
#PermitEmptyPasswords no
# Changed to no per AWS
#PasswordAuthentication no

Now restart SSHD process.

$ sshd
sshd re-exec requires execution with an absolute path
root@domU-12-31-39-00-7C-94:[/etc/ssh]
$ which sshd
/usr/sbin/sshd
root@domU-12-31-39-00-7C-94:[/etc/ssh]
$
root@domU-12-31-39-00-7C-94:[/etc/ssh]
$ service /usr/sbin/sshd restart
/usr/sbin/sshd: unrecognized service
root@domU-12-31-39-00-7C-94:[/etc/ssh]
$ service sshd restart
Stopping sshd:                                             [  OK  ]
Starting sshd:                                             [  OK  ]
root@domU-12-31-39-00-7C-94:[/etc/ssh]
$

Now SSHD process is started with password authentication enabled. If we remove the key from the putty profle and open a telnet connection, we'll be prompted for userid and password.

We should be able use a valid linux user with password to login.

login as: oracle
oracle@ec2-23-22-205-159.compute-1.amazonaws.com's password:
oracle@domU-12-31-39-00-7C-94:[/home/oracle]
$
oracle@domU-12-31-39-00-7C-94:[/home/oracle]
$
oracle@domU-12-31-39-00-7C-94:[/home/oracle]
$

That's it. Be careful when using this technique.

ORA-32004: obsolete or deprecated ...

AS I was playing around with Oracle 11g instance, I set the log_archive_start parameter with SPFILE option.

Unfortunately this parameter is obsolete since 10g (oh never realised), so now every time I start this instance Oracle gives me a warning.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes
Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
Database opened.
SQL>

So now I want to reset or remove this parameter. Well solution comes from Tom Kyte's forum.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2200190221847

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

SQL>

Let us find where the init<SID>.ora file went...

oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/dbs]
$ ll
total 28
-rw-rw---- 1 oracle oinstall 1544 Feb 15  2010 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Feb 15  2010 hc_odmdb.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r--r-- 1 oracle oinstall  900 May 25 21:19 initodmdb.ora
-rw-r----- 1 oracle oinstall   24 Feb 15  2010 lkODMDB
lrwxrwxrwx 1 oracle oinstall   31 Feb 19  2010 orapwodmdb -> /u02/admin/odmdb/dbs/orapwodmdb
drwx------ 2 oracle oinstall 4096 Feb 15  2010 peshm_DBUA0_0
drwx------ 2 oracle oinstall 4096 Feb 15  2010 peshm_odmdb_0
lrwxrwxrwx 1 oracle oinstall   36 Feb 19  2010 spfileodmdb.ora -> /u02/admin/odmdb/dbs/spfileodmdb.ora
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/dbs]
$ vi initodmdb.ora

Oh yes! we found it now let us edit it and remove the obsolete parameter.

odmdb.__db_cache_size=167772160
odmdb.__java_pool_size=4194304
odmdb.__large_pool_size=4194304
odmdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
odmdb.__pga_aggregate_target=247463936
odmdb.__sga_target=465567744
odmdb.__shared_io_pool_size=0
odmdb.__shared_pool_size=281018368
odmdb.__streams_pool_size=0
*.audit_file_dest='/u02/admin/odmdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/oradata/odmdb/control01.ctl','/u02/flash_recovery_area/odmdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='odmdb'
*.db_recovery_file_dest='/u02/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=odmdbXDB)'
*.log_archive_start=FALSE
*.memory_target=713031680
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

Now, after we edited the init<SID>.ora file we'll recreate the spfile FROM pfile.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 25 21:37:18 2012

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

Connected to an idle instance.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes
Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
Database opened.
SQL>
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance


SQL>

Oops.. The database was started with SPFILE (by default) so PFILE is not in effect.
So let us restart the database with PFILE. The default PFILE location is $ORACLE_HOME/dbs/init$ORACLE_SID.ora in my instance.

So, we'll shutdown the database, start it up with PFILE option, recreate SPFILE from PFILE, and then restart the database.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 25 21:57:38 2012

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

Connected to an idle instance.

SQL> startup PFILE="/u01/app/oracle/product/11.2.0/db_1/dbs/initodmdb.ora"
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes


Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;

File created.

SQL>

NOTE: There is no ORA warning. We'll just restart the database now and we are good to go... The database started using SPFILE :-)

Also we are in ARCHIVELOG mode.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
        ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes
Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     135
Next log sequence to archive   137
Current log sequence           137
SQL>

Next we'll explore how to manage a database in archivelog mode.

Oracle 11g - Changing to ARCHIVELOG mode

There are several blogs and writeups on this topic. This is one of the most fundamental topic in Oracle database administration. This post describes my experience in AWS Oracle instance.

I created the Oracle instance using AWS EC2 instance. By default (in this instance) the DB instance was in NOARCHIVELOG mode.

This can be verified by using SQL plus or EM interface.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 8 19:40:28 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name, log_mode from v$database;

NAME      LOG_MODE
--------- ------------
ODMDB     NOARCHIVELOG

SQL>
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     44
Current log sequence           46
SQL>

Let us issue the command to enable the archive mode.

SQL> alter system set log_archive_start=TRUE scope=spfile;

System altered.

SQL>

Now we shutdown the database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Resart the database.

SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceRACLE instance started.
Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes
Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
Database opened.
SQL>

OOOPS... That did NOT seem to work!!!

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     134
Current log sequence           136
SQL>

... So what went wrong? We set log_archive_start=TRUE parameter. This is an OBSOLETE parameter since 10g. Well what do we do then?

Lets try again...

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes
Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     134
Next log sequence to archive   136
Current log sequence           136
SQL>

Well now the database is in ARCHIVELOG Mode :-)

 

JOINs in Oracle (some fundamental thoughts)

In many forums and in personal experience I have come across people asking some basic questions on JOINS in Oracle (for that matter in general relational databases).

One of the five basic relational databases (in theory and practice) is JOIN operation (strictly speaking it is cartesian product). The other four are SELECTION, PROJECTION, UNION and MINUS. More on this can be found at wiki pages: http://en.wikipedia.org/wiki/Relational_algebra. You may feel it little boring, but trust me it is very rich ;-)

As Tom Kyte said, "Oracle (database) is born to Join". That is so very true and yet many developers and programmers almost fear joining tables. Join operation in Oracle (and in other databases) because of two different syntaxes: traditional and ANSI. One of the effective way, in addition to reading the documentations, to learn is to try out some examples.

In this blog post, I hope to provide some basic introduction to JOINs in Oracle. The basic documentation can be found in Oracle manuals.

First let us do some setup.

-- SETUP for examples...
drop table A;
drop table B;
drop table C;

create table a (ca int, sa varchar2(50));

create table b (cb int, sb varchar2(50));

create table c (cc int, sc varchar2(50));

insert into A
select * from
(
select level myid, to_char( to_date( level,'J'),'Jsp') mystr
from dual connect by level < 11
);
commit;

insert into B
select * from
(
select level myid, to_char( to_date( level,'J'),'Jsp') mystr
from dual connect by level < 21
)
where mod(myid,2) = 0;
commit;

insert into C
select * from
(
select level myid, to_char( to_date( level,'J'),'Jsp') mystr
from dual connect by level < 11
)
where myid < 6;
commit;

The SQL statements above sets up three tables and populate them with some "meaningless" data. I believe meaninglessness important. When traditional employees-department or books-library examples are used there is a presumption on the meaning of data resulting in the focus and expectation clouding the essence, which is JOIN operation.

Let us verify our data / setup

-- Verify the setup
select * from A;

select * from B;

select * from C;

First we'll see the most basic join operation.

Requirement EQUI JOIN:

To select rows from A with MATCHING rows from B. Use A join B or B join A

-- **************************************************************
-- Requirement EQUI JOIN 1. (AKA INNER JOIN)
-- To select rows from A and MATCHING rows from B
-- use A JOIN B or B JOIN A 
--
-- ANSI SQL syntax
select * from 
A JOIN B
on (A.CA = B.CB)
;
-- **** OR ****
select * from 
B JOIN A
on (A.CA = B.CB)
;
-- OR Traditional Oracle syntax
select * from A, B
where A.CA = B.CB
;

Requirement EQUI JOIN 2.

To select rows from A and MATCHING rows from B and matching rows from C
Use A JOIN B JOIN C

-- **************************************************************
-- Requirement EQUI JOIN 2. (AKA INNER JOIN)
-- To select rows from A and MATCHING rows from B and matching
-- rows from C
-- use A JOIN B JOIN C
--
-- ANSI SQL syntax
select * from 
A JOIN B
on (A.CA = B.CB)
JOIN C
on(B.CB = C.CC)
;
-- OR Traditional Oracle syntax
select * from A, B, C
where A.CA = B.CB
  and B.CB = C.CC
;

Requirement OUTER JOIN 1:
To select ALL rows from A and ONLY MATCHING rows from B
Use A LEFT OUTER JOIN B or B RIGHT OUTER JOIN A

-- **************************************************************
-- Requirement OUTER JOIN 1.
-- To select ALL rows from A and ONLY MATCHING rows from B
-- use A LEFT OUTER JOIN B or B RIGHT OUTER JOIN A
--
-- The columns for table B will have NULL value where a
-- match for A is not found
--
-- ANSI SQL syntax
select * from 
A LEFT OUTER JOIN B
on (A.CA = B.CB)
;
-- **** OR ****
select * from 
B RIGHT OUTER JOIN A
on (A.CA = B.CB)
;
-- OR Traditional Oracle syntax
select * from A, B
where A.CA = B.CB(+)
;

NOTE: The queries above are EQUIVALENT, theay return the SAMe result set; However notice the order in which they resturn the rows.

There is no meaning for ORDER in relation algebra/mathematics. They all are RESULT SETS.

The only and ONLY way to ensure the result set is ordered is by using ORDER BY clause.

Requirement OUTER JOIN 2:
To select ALL rows from B and ONLY MATCHING rows from A
Use B LEFT OUTER JOIN A or A RIGHT OUTER JOIN B

-- **************************************************************
-- Requirement OUTER JOIN 2.
-- To select ALL rows from B and ONLY MATCHING rows from A
-- use B LEFT OUTER JOIN A or A RIGHT OUTER JOIN B
--
-- The columns for table A will have NULL value where a
-- match for B is not found
--
-- ANSI SQL syntax
select * from 
B LEFT OUTER JOIN A
on (A.CA = B.CB)
;
-- **** OR ****
select * from 
A RIGHT OUTER JOIN B
on (A.CA = B.CB)
;
-- Traditional Oracle syntax
select * from A, B
where A.CA(+) = B.CB
;

Requirement OUTER JOIN 3
To select ALL rows from A and ONLY MATCHING rows from B AND select those rows from C that match rows from B

-- **************************************************************
-- Requirement OUTER JOIN 3.
-- To select ALL rows from A and ONLY MATCHING rows from B
-- AND select those rows from C that match rows from B
--
-- ANSI SQL syntax
select * from 
A LEFT OUTER JOIN B
on (A.CA = B.CB)
 LEFT OUTER JOIN C
on (B.CB = C.CC)
;
-- OR Traditional Oracle syntax
select * from A, B, C
where A.CA = B.CB(+)
  and B.CB = C.CC(+)
;

----------------------------------------------------------
-- and So on...
 

We started of with CARTESIAN PRODUCT... what is that?

Requirement FULL JOIN:
To select ALL rows from A and ALL rows from B AND MATCH the rows from A and B that match, but return even the unmatching rows. 

-- **************************************************************
-- Requirement FULL JOIN 3.
-- To select ALL rows from A and ALL rows from B
-- AND MATCH the rows from A and B that match, but return even 
-- the unmatching rows.
--
-- ANSI SQL syntax
select * from 
A FULL OUTER JOIN B
on (A.CA = B.CB)
;
-- OR Traditional Oracle syntax
-- There is no equivalent for FULL OUTER join in traditional 
-- syntax. (Though it can be achieved using other means)
-----------------------------------------------------------------

Happy Joining and good luck!

Grant SELECT_CATALOG_ROLE to user

In last blog we connected locally and remotely to our AWS Oracle instance.

We used user HR to login to DB instance. However we were unable to select from some basic V$ views.

Let us explore that in blog.

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus hr/hr@odmdb

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 20:11:16 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;
select * from v$instance
              *
ERROR at line 1:
ORA-00942: table or view does not exist

We get the error ORA-00942 that V$INSTANCE view does not exist. That is not TRUE. It does exist; We know it exists.

However the HR user does not have permission (privilege) to see these views (V$ views). Let us grant the required permission and try again.

SQL> connect / as sysdba;
Connected.
SQL> grant select_catalog_role to hr;

Grant succeeded.

SQL> connect hr/hr;
Connected.
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 odmdb
domU-12-31-39-03-BD-92
11.2.0.1.0        30-APR-12 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO


SQL>

The user must have SELECT_CATALOG_ROLE to select from V$ views.

More in our next blog.

Grant SELECT_CATALOG_ROLE to user

In last blog we connected locally and remotely to our AWS Oracle instance.

We used user HR to login to DB instance. However we were unable to select from some basic V$ views.

Let us explore that in blog.

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus hr/hr@odmdb

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 20:11:16 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;
select * from v$instance
              *
ERROR at line 1:
ORA-00942: table or view does not exist

We get the error ORA-00942 that V$INSTANCE view does not exist. Tha is not TRUE. It does exist; We know it exists.

However the HR user does not have permission (privilege) to see these views (V$ views). Let us grant the required permission and try again.

SQL> connect / as sysdba;
Connected.
SQL> grant select_catalog_role to hr;

Grant succeeded.

SQL> connect hr/hr;
Connected.
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 odmdb
domU-12-31-39-03-BD-92
11.2.0.1.0        30-APR-12 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO


SQL>

The user must have SELECT_CATALOG_ROLE to select from V$ views.

More in our next blog.

Remote connection to Oracle instance in AWS

Last blog entry we connected successfully to Oracle instance locally. Now we'll create the TNSNAMES.ORA entry and connect using remote connection. There are many components that come in to play when connecting to Oracle instance using remote mechanism.

  1. Identify the instance SID
  2. Ensure LISTENER services are up and running
  3. The "client side" TNSNAMES.ORA file exists.
  4. The TNSNAMES.ORA file has appropriate entries for the instance that you are trying to connect
  5. Unix/Linux (OS) environment is rightly pointing to correct TNSNAMES.ORA file. This is achieved by using TNS_ADMIN environment variable.

If any of these components are not propoerly configured then Oracle will retrun different messages. Unfortunately, for a beginner this becomes complex. I'll attempt to cover this, so it might help others.

Note: There ae tons of documentations, websites, blogs, and forums that address this.

SID of the Oracle instance:

normally this will be known or DBA can provide this to you. However, looking at the processes (default) running you can infer what the SID is. In Oracle 11g instance in AWS, we see the following.

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ pwd
/home/oracle
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin)
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ ps -ef | grep ora
oracle    4923     1  0 Apr23 ?        00:00:00 /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle   25484     1  0 18:39 ?        00:00:00 ora_w000_odmdb
root     25502 14542  0 18:40 ?        00:00:00 sshd: oracle [priv]
oracle   25504 25502  0 18:41 ?        00:00:00 sshd: oracle@pts/0
oracle   25505 25504  0 18:41 pts/0    00:00:00 -bash
oracle   25529 25505  0 18:41 pts/0    00:00:00 ps -ef
oracle   25530 25505  0 18:41 pts/0    00:00:00 grep ora
oracle   26119     1  0 Apr30 ?        00:00:00 ora_pmon_odmdb
oracle   26121     1  0 Apr30 ?        00:00:00 ora_vktm_odmdb
oracle   26125     1  0 Apr30 ?        00:00:00 ora_gen0_odmdb
oracle   26127     1  0 Apr30 ?        00:00:00 ora_diag_odmdb
oracle   26129     1  0 Apr30 ?        00:00:00 ora_dbrm_odmdb
oracle   26131     1  0 Apr30 ?        00:00:01 ora_psp0_odmdb
oracle   26133     1  0 Apr30 ?        00:00:05 ora_dia0_odmdb
oracle   26135     1  0 Apr30 ?        00:00:00 ora_mman_odmdb
oracle   26137     1  0 Apr30 ?        00:00:00 ora_dbw0_odmdb
oracle   26139     1  0 Apr30 ?        00:00:03 ora_lgwr_odmdb
oracle   26141     1  0 Apr30 ?        00:00:00 ora_ckpt_odmdb
oracle   26143     1  0 Apr30 ?        00:00:04 ora_smon_odmdb
oracle   26145     1  0 Apr30 ?        00:00:00 ora_reco_odmdb
oracle   26147     1  0 Apr30 ?        00:00:02 ora_mmon_odmdb
oracle   26149     1  0 Apr30 ?        00:00:00 ora_mmnl_odmdb
oracle   26151     1  0 Apr30 ?        00:00:00 ora_d000_odmdb
oracle   26153     1  0 Apr30 ?        00:00:01 ora_s000_odmdb
oracle   26202     1  0 Apr30 ?        00:00:00 ora_qmnc_odmdb
oracle   26218     1  0 Apr30 ?        00:00:05 ora_cjq0_odmdb
oracle   26226     1  0 Apr30 ?        00:00:00 ora_q000_odmdb
oracle   26228     1  0 Apr30 ?        00:00:00 ora_q001_odmdb
oracle   26280     1  0 Apr30 ?        00:00:00 ora_smco_odmdb
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$

this command list the OS processes running. Note: odmdb is the Oracle instance SID. This is by default (could have been changed in your specific situation).

At this point we are login to AWS Linux instance as oracle (os user). By default listener for our Oracle instance odmdb is up and running. Let us ensure that.

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-MAY-2012 18:53:14

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=domU-12-31-39-03-BD-92)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-APR-2012 22:21:54
Uptime                    8 days 20 hr. 31 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/domU-12-31-39-03-BD-92/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=domU-12-31-39-03-BD-92)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=domU-12-31-39-03-BD-92)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "odmdb" has 1 instance(s).
  Instance "odmdb", status READY, has 1 handler(s) for this service...
Service "odmdbXDB" has 1 instance(s).
  Instance "odmdb", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$

With this a current state, let us try connecting to our Oracle instance (odmdb) using HR as Oracle user.

$ sqlplus hr/hr@odmdb

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 18:55:06 2012

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
oracle@domU-12-31-39-03-BD-92:[/home/oracle]

sqlplus will attempt to connect (and prompt) 3 times. In future I'll truncate this to first attempt.

We got ORA-012154:  TNS:could not resolve the connect identifier specified

Unfortunately, this message could come for several different reasons (root causes). Basicaly, it means that the connect identifier (odmdb) we specified is not resolvable using current TNSNAMES.ORA specification.

This could be because of several reasons, like:

  1. TNSNAMES.ORA file is not accessible. (does not exist).
  2. The SID specified in the file is not matching (wrong) etc.

Let us now ensure TNSNAMES.ORA file exists (and it has proper entry for odmdb). The file did not eixst in any directory within the path; however it does (sample) in default location. The sample file has no entry for our Oracle instance (odmdb).

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ pwd
/home/oracle
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ which tnsnames.ora
/usr/bin/which: no tnsnames.ora in (/u01/app/oracle/product/11.2.0/db_1/bin:/u01/app/oracle/product/11.2.0/db_1/jdk/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin)
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ !cd
cd /u01/app/oracle/product/11.2.0/db_1/network/admin/samples
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$ ls
listener.ora  sqlnet.ora  tnsnames.ora  tnsnames.ora.orig
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$

Let us add an entry for odmdb.

# The following is the general syntax for any entry in
# a tnsnames.ora file. There could be several such entries
# tailored to the user's needs.

odmdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = domU-12-31-39-03-BD-92)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = odmdb)
    )
  )

If this entry is not accurate, we'll get the same error as before. We'll explore this later.

This is valid entry.

Now, let us try connecting again.

$ sqlplus hr/hr@odmdb

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 19:21:38 2012

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Oh well, May be this TNSNAMES.ORA file is not in effect? That is correct. The os variable TNS_ADMIN value points to the directory where effective TNSNAMES.ORA file could be found. Let us check the value in our environment.

oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$ env | grep TNS
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]

Uh Oh... The value is not set. Let us set it  and then try again. We'll use export command (since we are in bash)

oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$ export TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin/samples
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$ env | grep TNS
TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin/samples
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$ sqlplus hr/hr@odmdb

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 19:37:53 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$

VOILA!!!

The connection using "remote" mechanism is successful. 

Normally, we can set up the TNS_ADMIN value in our .profile script.

In our next blog we'll attempt some basic work in Oracle instance (using sqlplus).

Experiment with new Oracle instance in AWS

We initiated and opened up AWS linux instance with Oracle 11g database.

In this blog, we'll explore several errors encountered with Oracle DB instance. Most of these errors (including messages) are due to operator errors.

We'll connect to AWS instance as oracle user.

 

login as: oracle
oracle@ec2-107-20-86-85.compute-1.amazonaws.com's password:
Last login: Mon Apr 30 17:29:17 2012 from 98.235.186.132
oracle@domU-12-31-39-03-BD-92:[/home/oracle]$

The oracle account was enabled for userid/password login into AWS instance.

Once logged in, we attempt starting sqlplus and start the database... sounds reasonable?

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:03:18 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>

Oh! looks like oracle instance is already up and running.

Let us check it out:

SQL> select * from dual;

D
-
X

SQL>

Next we'll try loggin in to Oracle db instance using HR userid.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus hr/hr@orcl

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:06:37 2012

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:

So, what is going on? DB instance is up, but I cannot login using one of the standard account HR.

ORA-12154 error indicates that TNS could not resolve @orcl identifier. Since I am using @ connection mechanism, client(sqlplus) requires TNSNAMES file to point to the DB instance. We don't have that file yet.

Previous login attempt with "/ as sysdba" was NOT using TNSNAME file, but only LOCAL connection.

To connect to LOCAL DB instance (which is what we have), we should set ORACLE_SID environment variable to the value of SID. Our instance SID is "orcl". So let see that...

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ set ORACLE_SID=orcl
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:15:12 2012

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

ERROR:
ORA-28000: the account is locked

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$

That is better... HR user can attempt, but the account is locked. Note we are connecting LOCAL (not using @) as HR user.

We'll unlock the HR user and retry...

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:26:32 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user hr account unlock;

User altered.

SQL> alter user hr identified by *******;

User altered.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:27:15 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Viola!!!

Note that we had already set the environment variable ORACLE_SID set to orcl; and with HR account unlocked we are now able login to DB with orcale account. how about remote login (using @)

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus hr/hr@orcl

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:30:48 2012

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$

UH OH... we still do not have TNS resolver. Basically we do not have a TNSNAMES.ORA file in place, which is required for @ connection (remote connection).

Next blog entry we are going to attempt this.

Opening AWS linux instance for user/password login

Once the AWS Oracle instance was created, by default the root login using PKI is available.

You would need to add userid/password login  (not recommended for security reasons) for other administrative users. This may be desirable in development ot testing environment.

  1. Login as root (using PKI).
  2. Modify sshd_config under /etc/ssh/ directory, to allow passwordlogin.
  3. restart sshd.

These steps will allow userid/password based login to AWS instance.

login as: root
Authenticating with public key "imported-openssh-key"
Last login: Tue Apr 24 18:13:28 2012 from 98.235.186.132
root@domU-12-31-39-03-BD-92:[/root]
$ cd /etc/ssh
root@domU-12-31-39-03-BD-92:[/etc/ssh]
$ ls
moduli            ssh_host_dsa_key.pub  ssh_host_rsa_key
ssh_config        ssh_host_key          ssh_host_rsa_key.pub
ssh_host_dsa_key  ssh_host_key.pub      sshd_config
root@domU-12-31-39-03-BD-92:[/etc/ssh]
$ vi sshd_config

Following parameter was changed (the default is NO)

# RhostsRSAAuthentication and HostbasedAuthentication
#IgnoreUserKnownHosts no
# Don't read the user's ~/.rhosts and ~/.shosts files
#IgnoreRhosts yes

# To disable tunneled clear text passwords, change to no here!
#PasswordAuthentication yes
#PermitEmptyPasswords no
# Changed to no per AWS
PasswordAuthentication yes  # <<<<<<<<<< CHANGED

# Change to no to disable s/key passwords
#ChallengeResponseAuthentication yes
ChallengeResponseAuthentication no

# Kerberos options
#KerberosAuthentication no
#KerberosOrLocalPasswd yes
#KerberosTicketCleanup yes
#KerberosGetAFSToken no

At this point, we'll use Oracle (linux account) to login using userid/password.

login as: oracle
oracle@ec2-107-20-86-85.compute-1.amazonaws.com's password:
Last login: Mon Apr 30 16:59:02 2012 from 98.235.186.132
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$

The password is the one that was created when the instance was created. 

Oracle MERGE statement - Consistent READ

MERGE Statement & constraint violations

Oracle database ALWAYS provides Consistent-Read. This is a very powerful feature in Oracle and there are hundereds of articles on-line about this.

Simple description of Consistent Read is

When a (reading) query starts, it is important that the values of the rows selected are the same as when the query started, even if another session has changed those rows. This is refered to as read consistency. Read consistency is achieved through the SCN (system change number). (REF: http://www.adp-gmbh.ch/ora/concepts/consistent_read.html)

A somewhat bit more technical details can be found at http://dioncho.wordpress.com/2009/04/18/simple-and-stupid-test-on-consistent-read/

However, what's Consistent Read got to do with MERGE statement that this article is about?

MERGE statement allows a developer to take a source result set (result of a query) and MERGE that with a target result set. Syntax is documented at http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9016.htm.

Key points from the doumantation (above) are

  1. Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.
  2. This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.
  3. MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.

One of our developer developed a very complex MERGE statement. The program worked fine for most of the time, however at seeming random instances (for spcific input) the statement failed with Unique Key constraint Violation

It took a deeper understanding of consistent read to not only resolve the problem, but also leverage MERGE statement better in future. I hope our experience help others who are learning about Oracle.

I'll explain this experience with simplified example.

Version

select * from v$version where rownum < 2;

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Setup

We'll set up two small tables src (source) and tgt (target with PK).

drop table tgt;

drop table src;

create table tgt as
  select 1 c, 'A' str from dual ;

alter table tgt add primary key (c);

create table src as
  select 1 c, 'B' str from dual union
  select 2 c, 'B' str from dual union
  select 2 c, 'C' str from dual 
 ;

Note: The tgt has a primary key and row with value 1 already exists.

If the following MERGE state is executed, what will be the result?

merge into tgt
  using  src
     on (tgt.c = src.c)
  when matched then
     update set tgt.str = src.str
  when not matched then
     insert (tgt.c,tgt.str)
     values (src.c, src.str)
;

This will result in ERROR: SQL Error: ORA-00001: unique constraint (HR.SYS_C0021834) violated.

Developer was very flustered with this error and called it a bug in Oracle. I am not sure if other databases behave this way.

The expectation was based "row-by-row" thought process.

It was expected that when a matching row is encountered the row will be updated, but when "first non-matching row" is encountered it will be treated as an INSERT and subsequent rows with same key will be treated as UPDATE.

This was misplaced expectation.

Oracle ALWAY performs in read-consistent manner.For Merge statement both the source and target result sets are determined at the time when execution of the  statement begins .

Therefore, in the example above both rows from source (src) with key value of 2 are treated as INSERT. Not the first one as INSERT and second one as UPDATE.

With this understanding, resolution of the problem is simple, but with misplaced expectation the struggle continues.

Hope this will save others from some undue struggle.

 

Multi-Table Insert

It is important to understand the fundamentals of the tool before using it, especially in production. We learnt this, costly lesson through struggle. In this BLOG, I'll document my experience, so it may help others.

 

Couple of years back, one of the developer learnt about multi-table insert ability in Oracle. We were running Oracle version 10g and in our application many primary/foriegn keys were generated using sequences.

 

The new code worked fine. Performance improvement (using multitable insert) in batch programs, instead of multiple insert statement, were GREAT. Significant (but not effective testing) was performed. Everything passed and code went to production.

However, after the code was in production for over 1 year, the batch programs (many of them) occassionally resulted in ORA-02291: integrity constraint (nnnnnnnn) violated - parent key not.

 

And then the frequencey of failure increased to almost multiple  time a day. The processes run many times a day.

 

Since the error was not "reproducible" consistently in QA or development environment, the struggle continued. Developers blaming DBA and DBA blaming developers; Every one blaming Everyonelse. Furstration grew.

 

Now, a quick search for Note 265826.1 (in google, or MOS) might have resolved everything, however no one even thought of  looking under the cover or search for this unknown MOS note.

Let us take a look at some sample code:

Setup:

For evaluating the behavior of Multi table insert using Oracle sequence as keys, with Parent-child (Master-Detail) relationships between tables, we'll try a simple example. Here is the setup code.

drop table tb;
drop table ta;

create table ta (a number not null primary key , 
                 da varchar2(30) not null);

create table tb (b number not null references ta(a) , 
                db varchar2(30) not null);

drop sequence ts;
create sequence ts;

When following SQL is executed, everything works fine.

insert all
 into  ta (a, da) values (ts.nextval, xx)
 into  tb (b, db) values (ts.currval, xx)
select 'XXXXX' xx  from dual connect by level < 500;

However, when you increase the the number of records to be inserted as shown below, we ran into ORA-02291.

For eg.

insert all
 into  ta (a, da) values (ts.nextval, xx)
 into  tb (b, db) values (ts.currval, xx)
SELECT 'XXXXX' xx  from dual connect by level < 500000;

This is documented BUG of Multi-Table Insert: Note 265826.1. This was intitated in 2004. The workaround is described however the BUG is not corrected.

Developer tried adding ORDER BY clause and spent four days, struggling to do various alternatives.

Hopefully this will be addressed (if addressable) in some release. It still exists in 11.2.0.2 release.

 

MOS note excerpt:

This issue is an open bug:
<bug:2891576> MULTI TABLE INSERT (INSERT ALL) FAILS WITH ORA-2291
Based on the above:
"The order of the tables into which Oracle inserts data is not determinate. Therefore, before
issuing a multitable insert statement, you should defer any constraints and disable any triggers
that depend on a particular table order for the multitable insert operation."

FIX

WORKAROUND:
1. Disable the foreign key when run such MultiPath Inserts.
2. Use DEFERRED CONSTRAINTS so the checkout happens only at Commit time.
About Deferred Constraints check Metalink <Note:73647.1> "Deferred Constraints Example"

 

References:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6915127515933

MOS document:  Note 265826.1

 

Hope this helps others.

Oracle DB Instance in AWS

Creating an Oracle 11g instance in AWS

This document assumes, that you already have an AWS account and you are able to login using any standard net browser.After logging into AWS (Amazon Web Services) EC2 site, click on Launch Instance button.

NOTE: Starting this instance using this documentation will incur some monthly financial cost.

 

We'll user the instance ami-117b9778. We can narrow down the earch by typing in some key words. I used Oracle.

 

We’ll use a specific instance: ami-117b9778 instance store.

We’ll create a “small instance”.

No specific preference for zone.

 

In the next screen we’ll take default for all values.

Specifically note if you use monitoring, additional charges may apply.

Click on Continue…

At this point, we have chosen our AMI.

We’ll add one “tag information” for our instance…

We are ready to create KEY Pair for our instance. The Key-Pair is used for loggin into our instance with root access. With this set up, we’ll disable userid/password login to root and root access login will only be allowed using PKI key pair.

Note: we’ll be using Putty-SSH telnet to log into this instance as root. We’ll later create additional user-id which will have password enabled.

NOTE: This step is important, as we’ll create key for server and client. Save the client file safely in a directory and note the location/filename.

Give the key-pair a unique name, click on Create & Download your Key Pair button.

Note: You could proceed without key creation and create one later time.

You may be asked to save the file.

I clicked on Save As and chose a specific local location to save the file.

Note that the file type downloaded is PEM.

We have to choose a Security Group. We’ll go with default. We can change the values like port and accessibility later (or you can create a new security group).

We’ll go with default and click on continue…

Review the summary…

You have an opportunity to change your settings however you can also change many of these setting later.

We’ll click on Launch button

This step will take few minutes to launch the instance.

 

Click on Close to create an Oracle 11g instance. You’ll be returned to EC2 home.

Note: In the My Resources section, both Key Pair and Security Group has been updated.

Click on Instances on left panel to go to your instance page.

Note the instance is in pending state and status is initializing.

This may take some time. Just have some patience… It’ll come through :-)

Once your instance is instantiated, you find the following in your EC2 home.

Note the instance is in running state and status is green check.

Note: The name of the instance is empty. You can edit the name to something that you can recognize, by clicking on the field.

 

Now our next step is to connect to our instance.

To connect to our newly created AWS instance, we are going to use PUTTY-SSH telnet session.

You can download putty-ssh telnet client from here. I downloaded putty and puttygen from source forge.

PUTTYGEN will be used to generate private key from the PEM file that was downloaded from the KEY-PAIR of aws instance.

Generate Private Key using PUTTYGEN

Run PUTTYGEN by double clicking on the application

Click on LOAD… Note file type of PPK!!!!

Navigate to the directory where the PEM file was saved and select the PEM files (using all file types).

 

Open the PEM file.

DONOT Click on Generate: this allows you to move cursor over the BLANK space to generate randomized key, this will not work with your instance.

JUST click on SAVE PRIVATE KEY and Save Private Key, ENSURE to change the file type to ppk.

Close the PUTTYGEN application.

Connect to our AWS instance

Now we’ll use the PUTTY (telnet) application to connect to AWS instance.

When you open PUTTY application, you get the following dialog.

From the AWs instance definition (property) use Public DNS address

In our case the value is ec2-107-20-86-85.compute-1.amazonaws.com

Browse to the PPK file that was saved from PUTTYGEN.

Save PUTTY configuration and Open

Type in root and press ENTER

The Oracle 11g instance on AWS is READY to be configured.

Configure Oracle 11g instance

Ready for Oracle configuration…

The password and verification was entered and installation complete.

Check, if Oracle processes are running…

Check ID / GRP ???

root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ id
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)
root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin)
root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ groups
root bin daemon sys adm disk wheel
root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$

Logged in as ROOT to the linux instance, we’ll attempt to log into database instance.

Just to make sure the database is there, configured and started. We’ll use sqlplus.

$ ./sqlplus
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

SP2-0750 indicates we have not set ORACLE_HOME value. We’ll do that and try again.

root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1/

root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ ./sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 22:43:39 2012

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

Enter user-name: oracle
Enter password:

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

ORA-12162 indicates we have not set ORACLE_SID value. We’ll do that and try again.

root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ export ORACLE_SID=orcl
root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ ./sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 22:44:37 2012

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

Enter user-name: oracle
Enter password:

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

Login was successful (in a way) however ORA-01034 indicates that ORACLE database instance is not up and running. At this point we can connect to ORCL instance in NOLOG mode. 

root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]
$ ./sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 22:46:15 2012

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

SQL>

Nice… We are now connected to ORCL instance of the database. However, the DB instance is not up and providing any service. We’ll try to start the database instance.

SQL> startup

ORA-01031: insufficient privileges

SQL> exit;

root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin

The database exists, but we are unable to start the database as root is NOT a member of dba or oper groups. Only members of these groups are authorized (ORA-01031) to start or stop a DB instance.

In the next blog entry, I’ll continue with configuring the database instance and starting the database.

However, before we do that we should also create a non-root user at the OS level and open up the LINUX instance for password login.

Exploring ORA-04068 and ORA-04065

Since Oracle version 7.3 (probably even before that) ORA-04068 was encountered by developers and database administrators in PL/SQL packages. In this document I’ll attempt to de-mystify this error by exploring what the error means, its purpose and various causes and potential solutions.

One of the main reasons for mystery and frustration with this error is the fact that the package encountering the error can be very different from the object that actually caused the error. The package encountering ORA-04068 is dependent on the object causing the error. This dependency can be immediate (if we are fortunate) or could be arbitrarily long list of database objects. If we are really unfortunate the dependency can be also remote.

Fortunately, this error will resolve itself, upon re-invocation of the package that encountered the error.

There are several potential solutions/workarounds for this error available on the internet websites and blogs. Some of these recommendations are incorrect or dangerous.

As the new adage goes: Be careful of what you learn from Internet ;-)

Typically ORA-04068 is followed by ORA-04065 and ORA-06508, as shown below.

ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "pkg_c"
ORA-06508: PL/SQL: could not find program unit being called: "pkg_c"

This error is raised by PL/SQL engine upon on invocation of a package which maintains state and whose existing state is destroyed by some action. In such situation the PL/SQL engine is obligated to inform the client (invoker of the package) by raising exception ORA-04068.

Package State Maintenance

A PL/SQL package once invoked in a session will be resident (in the memory) through the life of the session. Specifically if the package has package level variables defined. The package level variables can be in the SPEC or in the BODY. The package will retain the values assigned to these variables for the life of the session.

When a package, say P, invoked in a session (say session 1) is maintaining state and any object that package P depends on is modified, compiled, then the next invocation of package P in session 1 will raise ORA-04068.

DEMO of ORA-04068

In this section, I’ll walk though the creation and some apparent solutions to this error. For simplicity, I will not address remote package dependencies in this document. (May be separate document)

Setup the environment for walk-though

For this section of DEMO, I’ll use a table MYT and two dependent packages. I’ll use only package specifications (and not bodies) for keeping the examples small. Package PKG_P depends on package PKG_C which in turn depends on table MYT.

Session A>drop table myt;
Table dropped.

Session A>drop package pkg_c;
Package dropped.

Session A>drop package pkg_p;
Package dropped.

Session A>create table myt (col1 int);
Table created.

Session A>CREATE OR REPLACE PACKAGE pkg_c
  2  AS
  3     TYPE t1 IS ARRAY (3) OF NUMBER;
  4     rc   myt%rowtype;    ---- <<<< Dependent on table MYT
  5     x1   NUMBER;
  6     y1   NUMBER;
  7  END;
  8  /

Package created.

Session A>CREATE OR REPLACE PACKAGE pkg_p
  2  AS
  3     x NUMBER;
  4     y NUMBER;
  5     j pkg_c.t1;     ---- <<<< Dependent on package PKG_C
  6  END;
  7  /

Package created.

Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.

Session A>

Scenario 1: Package PKG_C recompiled

Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.

Session A>alter package pkg_c compile;
Package altered.

Session A>exec pkg_P.Y := 2;
BEGIN pkg_P.Y := 2; END;
*
ERROR at line 1:

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "PKG_P" has been invalidated
ORA-04065: not executed, altered or dropped package "PKG_P"

Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.

Session A>alter package pkg_c compile;
Package altered.

Session A>exec pkg_P.Y := 2;

BEGIN pkg_P.Y := 2; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "PKG_P" has been invalidated
ORA-04065: not executed, altered or dropped package "PKG_P"              

Subsequent execution of the package in same session does not raise this error, as the state of the package has been reset.

When PKG_C is recompiled, Oracle invalidates all dependencies. Subsequent invocation of PKG_P results in ORA-06508 and auto-compile. This recompilation results in package state of PKG_P being discarded, resulting in ORA-04068.

Scenario 2: Package PKG_P recompiled

However recompilation of package PKG_P does NOT result in error in the same session. I believe after compilation of the package, Oracle resets state of the package in that session.

Scenario 3: Recreate or Alter the Table MYT

Similar to Scenario 1, described above, all the dependent packages on the object being altered are invalidated. Next invocation will result in auto-compile and if there is an existing state of the package, then it will result in ORA-04068.

DROP and CREATE

Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.

Session A>alter package pkg_p compile;
Package altered.

Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.

Session A>exec pkg_P.Y := 3;
PL/SQL procedure successfully completed.

Session A>

 

Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.

Session A>drop table myt;
Table dropped.

Session A>create table myt (col1 int);
Table created.

Session A>exec pkg_P.Y := 2;
BEGIN pkg_P.Y := 2; END;
*
ERROR at line 1:

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package “PKG_P” has been invalidated
ORA-04065: not executed, altered or dropped package “PKG_P”
ORA-06508: PL/SQL: could not find program unit being called: “PKG_P”
ORA-06512: at line 1

Session A>exec pkg_P.Y := 3;
PL/SQL procedure successfully completed.

Session A>

ALTER Table

For a package to maintain state, it must have package level variables declared.

Other connected sessions that may have invoked the package will also encounter this error on first invocation, after the package is recompiled.

Scenario 4: Package with package level constants versus variables

In this demonstration, I’ll use two different sessions.

Potential Solutions

I’ll describe some of the solutions and workarounds suggested on the internet for ORA-04068.

PRAGMA SERIALLY_REUSABLE

The PRAGMA directive SERIALLY_REUSABLE will definitely eliminate this error from occurring.

HOWEVER, this approach undermines the the objective of the package level variables.

 Blog Stats

  • Total posts(18)
  • Total comments(0)

Forgot your password?