What are nodeapps in an Oracle RAC environment?

Nodeapps are a standard set of Oracle application services that are automatically launched for RAC (Real Application Cluster). The following service are lunched by nodeapps:

•Virtual IP (VIP)
•Oracle Net Listener
•Global Services Daemon (GSD)
•Oracle Notification Service (ONS)

Nodeapp services that run on each node can be relocated to other nodes through the virtual IP.

How to use the dbms_metadata package to generate DDL for a user

-- Arg #1 schema name
-- Arg #2 DB Directory
whenever sqlerror exit 1
whenever oserror  exit 2
set long 100000 verify off feedback off
accept user_name prompt 'User name: '
accept directory_name prompt 'Directory name to write to: '
declare
begin
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
declare
no_grant      EXCEPTION;
PRAGMA EXCEPTION_INIT (no_grant, -31608);
CURSOR get_username
IS
SELECT username
FROM all_users
WHERE username = UPPER('&user_name');

file_handle   UTL_FILE.file_type;
stmt          CLOB;
BEGIN
file_handle := UTL_FILE.fopen(UPPER('&directory_name'), 'cr_user_&user_name..sql', 'w', 32767);

FOR l_user IN get_username
LOOP

-- USERS
stmt:=DBMS_METADATA.get_ddl('USER', l_user.username)||chr(10);
begin
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||'-- SYSTEM_GRANT'||chr(10);
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||DBMS_METADATA.get_granted_ddl('SYSTEM_GRANT', l_user.username)||chr(10);
exception
when no_grant then stmt := stmt||'-- no system grants'||chr(10);
end;
begin
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||'-- OBJECT_GRANT'||chr(10);
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||DBMS_METADATA.get_granted_ddl('OBJECT_GRANT', l_user.username)||chr(10);
exception
when no_grant then stmt := stmt||'-- no object grants'||chr(10);
end;
begin
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||'-- ROLE_GRANT'||chr(10);
stmt:=stmt||'---------------------------------------'||chr(10);
stmt := stmt||DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', l_user.username)||chr(10);
exception
when no_grant then stmt := stmt||'-- no role grants'||chr(10);
end;
begin
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||'-- TABLESPACE_QUOTA'||chr(10);
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',l_user.username)||chr(10);
exception
when no_grant then stmt := stmt||'-- no tablespace quota'||chr(10);
end;
begin
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||'-- DEFAULT_ROLE'||chr(10);
stmt:=stmt||'---------------------------------------'||chr(10);
stmt := stmt||DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', l_user.username)||chr(10);
exception
when no_grant then stmt := stmt||'-- no default role'||chr(10);
end;

UTL_FILE.put (file_handle, stmt);

end loop;
UTL_FILE.fclose (file_handle);
END;
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',FALSE);
end;

/
exit

How to use the dbms_metadata package to generate DDL for table

accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '

set long 90000
set longchunksize 300
set pagesize 0
set linesize 300
set trimspool on
set heading off
set verify off
set echo off
set feedback off
spool table_ddl.lst

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',TRUE);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
select dbms_metadata.get_ddl('TABLE',UPPER('&&table_name'),UPPER('&&owner'))
from dba_tables where table_name = UPPER('&&table_name');
exit

How to perform a clone from active database

With Oracle 11g, a new method to clone databases was introduced: "Clone from Active Database".
This method allows for cloning of a database using RMAN's DUPLICATE command, but without being dependent on any previous backups being available.

The following are my notes on how to use this feature to produce a clone from production database PRODDB01 to test database TESTDB01.

In file run_duplication.cmd:

connect catalog cataloguser/password@RMANCAT
connect target sys/clonedb@PRODDB01
connect auxiliary sys/clonedb@TESTDB01;
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
run {
debug io;
DUPLICATE TARGET DATABASE TO TESTDB01
FROM ACTIVE DATABASE
SKIP TABLESPACE TOOLS;
debug off;
}
exit

to execute:

rman cmdfile='run_duplication.cmd' debug trace='run_duplication.trc' log='run_duplication.log'


For this to work smoothly I followed the principles below:

• Script executed from the target server
• Auxiliary database must be in NOMOUNT mode
• Sys passwords must be identical on both source and target.
• Use the notation uid/pwd@<ORACLE_SID> when you Connect to both target and auxiliary. Do not use operating system authenticaion ( connect / ) - it will throw an error
• The db_block_size parameter must be identical on both source and target
• TNS connection must exist on both sides e.g. communication must be able to go from and from the source and the target. If not you will see the error

RMAN-03002: failure of Duplicate Db command at 07/21/2015 10:06:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on c1 channel at 07/21/2015 10:06:15
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

• I set the db_file_name_convert and the log_file_name_convert parameter in the auxiliary database. Make sure you check the location of the tempfiles, too, and include this in the db_file_name_convert value.
• I had no success using the TABLESPACE clause (which means an implicit EXCLUDE of all other tablespaces except those named explicitly)
• Using SKIP TABLESPACE ,, etc works, if the tablespaces are self-contained, meaning there are no referential constraints from object in one tablespace to another (even if both are skipped!)

Remember to reregister the duplicated database if it's backed up using a catalog database:

RMAN> connect target /
RMAN> list incarnation of database;
RMAN> register database;
RMAN> list incarnation of database;


The prerequisites for ACTIVE DATABASE cloning as laid out in the Oracle documentation are:

• At least one normal target channel and at least one AUXILIARY channel are required.
• When you connect RMAN to the source database as TARGET, you must specify a password, even if RMAN uses operating system authentication.
• The source database must be mounted or open. If the source database is open, then archiving must be enabled.
• If the source database is not open, then it must have been shut down consistently.
• When you connect RMAN to the auxiliary instance, you must provide a net service name. This requirement applies even if the auxiliary instance is on the local host.
• The source database and auxiliary instances must use the same SYSDBA password, which means that both instances must have password files.
• You cannot use the UNTIL clause when performing active database duplication. RMAN chooses a time based on when the online data files have been completely copied, so that the data files can be recovered to a consistent point in time.

How to set up ssh connection between two servers for user oracle

How to set up ssh connection between two servers for user oracle

1. connect to the remote system as user oracle
2. rm -rf $HOME/.ssh
3. /usr/local/bin/ssh-keygen -t dsa -f "$HOME/.ssh/id_dsa" -N ""
or: /usr/bin/ssh-keygen -t dsa -f "$HOME/.ssh/id_dsa" -N ""
4. cd $HOME/.ssh
5. cat id_dsa.pub >> authorized_keys2
6. check local connection, i.e. the following command should now work without having to enter a password (you might have to confirm with "yes" the first time, though):

ssh localhost date

If it doesn't work, i.e. if you're prompted to enter oracle's password, please check ownership and permissions on $HOME, it should owned by oracle:dba with permissions 755, i.e.:

ls -ld $HOME
drwxr-xr-x 10 oracle dba 5120 Jun 26 17:07 /app/oracle

7. Logon to the remote machine as user oracle.
8. Repeat the procedure above
9. When done, put the contents of id_dsa.pub in the other server's autorized_keys2 file.
10. Exchange is now done. You should be able to use ssh in both directions between the servers.

What is the difference between "in line" and "out of line" constraints?

The following examples create an "in line" constraint, since they both declare a constraint in the columns' definitions:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER PRIMARY KEY, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20)
);

An anonymous primary key constraint with a system generated name is created on the column PERSONELL_ID.
Optinally, give the constraint a specific name:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER CONSTRAINT STAFF_PK PRIMARY KEY, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20)
);

The following examples create an "out of line" constraint, since they are both declared separately from the columns' definitions:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20),
  PRIMARY KEY (PERSONELL_ID)
);

An anonymous primary key constraint with a system generated name is created on the column PERSONELL_ID.
Optinally, give the constraint a specific name:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20),
  CONSTRAINT STAFF_PK PRIMARY KEY (PERSONELL_ID)
);

The "ALTER TABLE" equivalent of the "in line" syntax is:

ALTER TABLE STAFF 
MODIFY PERSONELL_ID PRIMARY KEY;

Or with explicitly named constraint:

ALTER TABLE STAFF 
MODIFY PERSONELL_ID CONSTRAINT STAFF_ID PRIMARY KEY;

The "ALTER TABLE" equivalent of the "out of line" syntax is:

ALTER TABLE STAFF
ADD PRIMARY KEY (PERSONELL_ID);

Or with explicitly named constraint:

ALTER TABLE STAFF
ADD CONSTRAINT STAFF_PK PRIMARY KEY(PERSONELL_ID);

Note that NOT NULL constraints cannot be created "out of line".

How to use the different options for DATE datatype in Oracle

Datatypes that support dates and times come in two flavours:

Datetime data types, which are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE.

Interval data types, which are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Interval data types store time durations.

First, create a test table where the different types of DATE datatypes are bing used:

CREATE TABLE DATETYPETEST
    (TESTDATE1 DATE,
     TESTDATE2 TIMESTAMP,
     TESTDATE3 TIMESTAMP WITH TIME ZONE,
     TESTDATE4 TIMESTAMP WITH LOCAL TIME ZONE,
     TESTDATE5 INTERVAL YEAR TO MONTH,
     TESTDATE6 INTERVAL YEAR(4) TO MONTH,
     TESTDATE7 INTERVAL DAY TO SECOND,
     TESTDATE8 INTERVAL DAY TO SECOND(2)
     );

First I will test the Datetime data types:

INSERT INTO DATETYPETEST(TESTDATE1) VALUES(SYSDATE);
INSERT INTO DATETYPETEST(TESTDATE1) VALUES(TRUNC(SYSDATE));

TRUNC(SYSDATE)
17.01.2014 10:15:45
17.01.2014

INSERT INTO DATETYPETEST (TESTDATE2) VALUES (SYSTIMESTAMP);
INSERT INTO DATETYPETEST (TESTDATE2) VALUES (TRUNC(SYSTIMESTAMP));

TRUNC(SYSTIMESTAMP)
17.01.2014 10:15:45.978004
17.01.2014 00:00:00.000000

Interestingly enough, TRUNC on datatype TIMESTAMP does not strip of the time precision per se, Oracle simply sets it to zero altogether.

INSERT INTO DATETYPETEST (TESTDATE3) VALUES (SYSDATE);

TIMESTAMP WITH TIMEZONE
17.01.2014 10:15:46.000000 +01:00

INSERT INTO DATETYPETEST (TESTDATE4) VALUES (SYSDATE);

TIMESTAMP WITH LOCAL TIMEZONE
17.01.2014 10:15:46.000000

Remember that TIMESTAMP WITH LOCAL TIME ZONE does not store the time zone offset with the column value; however, when the column value is retrieved in a query, the value is sent in the user's local session time zone.

Let's move on to the interval data types.

First, I will insert the interval as a literal:

INSERT INTO DATETYPETEST (TESTDATE5) VALUES (INTERVAL '2-11' YEAR TO MONTH);

INTERVAL YEAR TO MONTH
+2-11

Below, I am using the TO_YMINTERVAL function to convert a string representing a number of years and a number of months into a INTERVAL YEAR TO MONTH datatype:

INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('14-01'));

INTERVAL YEAR TO MONTH
+14-01

The statement below, however, fails with ORA-01873: the leading precision of the interval is too small because used the default precision for year(2):

SQL> INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('2014-01'));

INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('2014-01'))
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small

So instead, let's insert the values into a column which allows for larger precision for the INTERVAL YEAR(n) TO MONTH datatype:

SQL> INSERT INTO DATETYPETEST (TESTDATE6) VALUES(TO_YMINTERVAL('2014-01'));

1 row created.

Of course, even values of less precision would be accepted here:

SQL> INSERT INTO DATETYPETEST (TESTDATE6) VALUES(TO_YMINTERVAL('14-01'));

1 row created.

INTERVAL YEAR(4) TO MONTH
+2014-01
+14-01

Again, I will first insert the interval as a literal:

INSERT INTO DATETYPETEST (TESTDATE7) VALUES(INTERVAL '2 6:30:01.222' DAY TO SECOND(3) ));

INTERVAL DAY TO SECOND
+02 6:30:01.222

Use the TO_DSINTERVAL function to convert the string representing a number of days, hours, minutes and seconds into an INTERVAL DAY TO SECOND datatype:

INSERT INTO DATETYPETEST (TESTDATE7) VALUES(TO_DSINTERVAL('7 12:30:000001'));

INTERVAL DAY TO SECOND
+07 12:30:01.000000

In the next example, I am limiting the number of fractional seconds according to the specifications of the column INTERVAL DAY TO SECOND(2):

INSERT INTO DATETYPETEST (TESTDATE8) VALUES(TO_DSINTERVAL('21 12:30:000001'));

INTERVAL DAY TO SECOND(2)
+21 12:30:01.00

The TO_DSINTERVAL is very flexible. Here is another post on the same topic.

An overview of conversion functions used with the INTERVAL data types can be found here

Sources: Oracle Documentation on intervals

What are AWR and ADDM and how are they related?

In Oracle 10g, one of the major changes to the database was the AWR and the ADDM.

AWR (Automatic Workload Repository)
Purpose: To collect statistics about the database automatically, without DBA intervention.
Snapshots of the database are taken every hour by default, and statistics derived from these snapshots are saved for 7 days by default.
The statistics are created through a separate background process called MMON (manageability monitor process).

ADDM (Automatic Database Diagnostic Monitor)
Purpose: To analyze the data in the AWR, without DBA intervention. The ADDM will analyze the statistics and save the results in the database. If the parameter STATISTICS_LEVEL is set to TYPICAL or ALL, ADDM is triggered every hour by default, right after a new snapshot has been taken by the AWR.

How to find the AWR snapshots currently available in the database

SET LINES 100 PAGES 999

SELECT SNAP_ID,SNAP_LEVEL, TO_CHAR(BEGIN_INTERVAL_TIME, 'dd.mm.yy hh24:mi:ss') "Starttime"
FROM DBA_HIST_SNAPSHOT 
ORDER BY 1
/

How to check that an Oracle database parameter is set in a unix shell script

Probably many ways to do the same thing, but I tend to spool database information to disk, then assign the values found to shell script variables, as in the following example, where I need to determined whether the parameters db_file_name_convert and log_file_name_convert is set in the database:

###############################################################
# Check that the db_convert_file_name and log_file_name_convert
# are both set. If not, abort script.
###############################################################
TRG_DB=${ORACLE_SID}
cat << EoF > check_${TRG_DB}_convert_params.sql
set termout off
set lines 200
set trimspool on
set heading off
set pages 0
set feedback off
set echo off
set verify off
col name format a30
col value format a40
spool check_${TRG_DB}_convert_params.lst
select name,value
from v\$parameter
where name in ('db_file_name_convert','log_file_name_convert');
exit
EoF
sqlplus -s / as sysdba @check_${TRG_DB}_convert_params.sql
CONV_DATA_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep db_file | awk '{print $2}'`
CONV_LOG_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep log_file | awk '{print $2}'`

case ${CONV_DATA_FILE_SET} in
'') echo db_file_name_convert is not set. Correct and retry operation.;exit;;
*) echo db_file_name_convert is set. Continuing...;;
esac

case ${CONV_LOG_FILE_SET} in
'') echo log_file_name_convert is not set. Correct and retry operation.;exit;;
*) echo log_file_name_convert is set. Continuing...;;
esac
exit

How to receive user response in a korn shell unix script

The following is valid for korn shell but not necessarily in other shell dialects, such as bash:


print -n "Do you want to proceed? [Y/N] "
read answer
[[ $answer == [yY] ]] || { echo "Exiting..."; exit 3; }

How to set up an alias that quickly shows you the running instances on a Solaris server

Thanks to Mats Strömberg who showed me this little trick on how to use the -o option with ps, to look directly at the command column of the output from ps.

oracle@testserv1:/home/oracle $ alias ri='ps -ef -o comm|grep -v grep|grep ora_[p]mon|sed '"'"'s/ora_pmon_//'"'"'|sort -n'
oracle@testserv1:/home/oracle $ ri
APITDB01
TESTDB01
TESTDB02
TESTDB03

How to deal with impdp error ORA-29913: error in executing ODCIEXTTABLEOPEN callout

I recently hit this error from one of my customers during import of a schema:

ORA-31693: Table data object "SH"."SALES_REG1_Q1" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout

After some searching on the internet, I found evidence of bug 8393456, and it is indeed confirmed on the Oracle Support website.

The problem seems to be related to cases where you use both PARALLEL (>1) and REMAP_SCHEMA at the same time.
Oracle versions affected are 11.2.0.1, 11.1.0.7 and 10.2.0.4.

I implemented the workaround by setting PARALLEL=1 and I can confirm that it solves the problem.

How to use v$session_longops to check long running processes

I am setting NLS_DATE_FORMAT so that the START_TIME and LAST_UPDATE_TIME will be more accurate.

SET TERMOUT OFF
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
SET TERMOUT ON
SET LINES 300
COL "CURRENTLY EXECUTING" FORMAT A35
COL OSUSER FORMAT A10
COL MACHINE FORMAT A20
COL USERNAME FORMAT A20
COL PROGRAM FORMAT A40
COL UNITS FORMAT A20
COL OPNAME FORMAT A10
COL START_TIME FORMAT A20
COL LAST_UPDATE_TIME FORMAT A20
COL SQL_ID FORMAT A15
SET VERIFY OFF
SET FEEDBACK OFF

SELECT
    S.SID,
    S.SERIAL#,
    S.OSUSER,
    S.MACHINE,
    S.USERNAME,
    S.PROGRAM,
    S.SQL_ID,
    SUBSTR(Q.SQL_TEXT,1,30) || '...' "CURRENTLY EXECUTING",
    L.OPNAME,
    L.SOFAR,
    L.TOTALWORK,
    L.UNITS,
    L.START_TIME,
    L.LAST_UPDATE_TIME,
    L.TIME_REMAINING "SECONDS LEFT",
    TO_TIMESTAMP(L.LAST_UPDATE_TIME,'DD.MM.YYYY HH24:MI:SS')-TO_TIMESTAMP(L.START_TIME,'DD.MM.YYYY HH24:MI:SS') "RUNNING FOR"
FROM V$SESSION_LONGOPS L JOIN V$SESSION S ON L.SID = S.SID
                         JOIN V$SQL Q ON S.SQL_ID = Q.SQL_ID
AND   S.SID = &SID
AND   S.SERIAL#  = L.SERIAL#
ORDER BY L.LAST_UPDATE_TIME DESC;

EXIT

Result:

SID	SERIAL#	OSUSER	MACHINE	USERNAME	PROGRAM	SQL_ID	CURRENTLY EXECUTING	OPNAME	SOFAR	TOTALWORK	UNITS	START_TIME	LAST_UPDATE_TIME	SECONDS LEFT	RUNNING FOR
408	2624	oracle	testserver1	SH	sqlplus@testserver1 (TNS V1-V3)	3w11rcbvd5a32	UPDATE SALES_Q1_DI_MASTERS SET ...	Table Scan	82432	82636	Blocks	06.03.2014 07:35:26	06.03.2014 09:44:22	19	+00 02:08:56.000000

How to deal with RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous

It turned out that the my RMAN session didn't know which DBID to perform the requested command against.

I checked my incarnation list, and observed the following:

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       198     PTADB01  2645386576       PARENT  6275306386559 10.04.11
1       2       PTADB01  2645386576       CURRENT 6278643127311 20.04.11
681     923     PTADB01  2663743751       PARENT  6275306386559 10.04.11
681     682     PTADB01  2663743751       CURRENT 6435835146938 16.11.11

Note how two of my incarnations are marked as being PARENT. Never saw this before.

A quick search on the internet pointed me to setting the DBID explicitly, so I vierfied that the DBID listed in the incarnation list above was indeed correct, by checking the backup logs files, and set it in the RMAN session:

RMAN> set DBID=2663743751;

So when the database name is not unique in the recovery catalog, you need to point out which one to use before RMAN can work.
The restore now worked, and the database could be mounted.

How to backup your controlfile to trace

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/fullpath/backup_controlfile.trc' [REUSE] [RESETLOGS | NORESETLOGS];

Note that if you do not specify either RESETLOGS or NORESETLOGS during the create command, oracle will include both versions in the resulting file, and you must pick the appropriate one for your situation.

REUSE will generate a backup controlfile statement which includes the REUSE keyword.
This in turn, indicates that when the "create controlfile" command is executed, you can reuse the existing physical control files on your server. If you don't, and the file exists from a previous database incarnation, Oracle will throw an error, so I prefer to leave it there.

What is the CJQ0 background process?

When starting the Oracle database you may see something like the following in the alert log:

Tue Mar 04 12:40:50 2014
CJQ0 started with pid=27, OS id=5526

This means your database is starting the optional background process for job queue processing

Oracle Database uses job queue processes to run user jobs.

The initialization parameter JOB_QUEUE_PROCESSES states the maximum number of job queue processes that can concurrently run on an instance.

Source: Oracle Documentation