What are literals?

The terms "literal" and "constant" are synonymous and refer to a fixed data value.

There are:

•Text Literals
Examples: 'JACK', 'BLUE ISLAND', '101'.

•Numeric Literals
Examples: 5001, +256

•Datetime Literals
Example: DATE '1998-12-25'.

Note that the ANSI (American National Standards Institute) date literal contains no time portion.

•Interval Literals
Examples:
INTERVAL '123-2' YEAR(3) TO MONTH
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)


Source: Oracle Documentation

How to instruct OUI to ignore prerequisites

./runinstaller -ignoreSysPrereqs

Using the procedure SEARCHFILES in DBMS_BACKUP_RESTORE

I found the following great article about how to list files in directory from within sqlplus here, written by David Marco. As pointed out by several oracle bloggers, there is very little documentation around for this package.

It just so happened that a user was in need of such functionality, and his code could be used straight-off.

However, my customer had already ammended a very useful functionality, by adding support for sending the directory name in as a parameter.

The code as it looks now:

CREATE OR REPLACE FUNCTION LIST_FILES( p_directory IN VARCHAR2, p_file_pattern IN VARCHAR2 default null)
RETURN file_array pipelined AS

l_path VARCHAR2(1024);
l_dummy VARCHAR2(1024);

BEGIN

   SELECT directory_path
   INTO l_path
   FROM all_directories
   WHERE directory_name = p_directory;

   sys.DBMS_BACKUP_RESTORE.SEARCHFILES(l_path, l_dummy);

   FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
                     FROM X$KRBMSFT
                     WHERE FNAME_KRBMSFT LIKE '%'|| NVL(p_file_pattern, FNAME_KRBMSFT)||'%' ) LOOP
      PIPE ROW(file_list.file_name);
   END LOOP;

END;
/

The call will thus be:

SELECT * FROM TABLE(LIST_FILES('MY_DIR','%*.dmp%'));

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.