Oracle Licensing

Oracle licensing is a relatively complex topic. The following links point to articles and documents that I have come across in my attempt to understand it. There are many potential scenarios, particularly with backup and recovery, disaster recovery and failover, and virtualization and clustering. Even after reading the articles and documents, and the Oracle documentation, it is probably necessary to have discussions with Oracle representatives to determine whether a particular scenario or architecture is correctly licensed. As well as database (engine/software) licenses, attention must be paid to database options and tuning packs.

Oracle Database Licensing

Oracle’s Licensing Rules – 5 Common pitfalls (techrepublic)
Oracle Licensing Information (pdf)
Oracle Software Investment Guide (pdf)

VMWare and Oracle Licensing

Oracle Database Licensing – raises questions

Oracle Licesing on VMware and Cloud

Oracle Database Licensing – again

1. A database link exists in the current database owned by CURRENTSCHEMA user (you don't know the password of the CURRENTSCHEMA user)

The database link was created by the application owner as follows:

  CREATE DATABASE LINK "'DBLINKNAME.DOMAIN.COM"
   CONNECT TO "TARGETSCHEMA" IDENTIFIED by password
   USING 'TNSNAMES_ALIAS';

2. Create a table in the target database as a snapshot of the current v$session dynamic view

                       create table x as select * from v$session;

3. grant select on the table to TARGETSCHEMA

                       grant select on x to targetschema;

4. Use anonymous pl/sql block below to execute a command on the new table to prove that the database link 
   exists, works, and is pointing at the right target database.

set serverout on size unlimited (if using sqlplus)

declare
strresult varchar2(1024);
stmt_cursor NUMBER;
rc          NUMBER;
found       NUMBER := 0;
p_schema    VARCHAR2(30) := 'CURRENTSCHEMA';
p_obj       VARCHAR2(30) := 'DBLINKNAME.DOMAIN.COM';
uid    dba_users.user_id%TYPE;
BEGIN
  select user_id into uid from dba_users where username=p_schema;
  stmt_cursor := dbms_sql.open_cursor;
  sys.dbms_sys_sql.parse_as_user(stmt_cursor, 
      'BEGIN SELECT COUNT(*) INTO :found 
             FROM user_db_links
             WHERE db_link = :p_obj;
       EXCEPTION WHEN OTHERS THEN :found := 0;
       END;', dbms_sql.NATIVE,
       uid);
  --dbms_sql.bind_variable(stmt_cursor, ':p_schema', p_schema);
  dbms_sql.bind_variable(stmt_cursor, ':p_obj', p_obj);
  dbms_sql.bind_variable(stmt_cursor, ':found', found);
  rc := dbms_sql.execute(stmt_cursor);
  dbms_sql.variable_value(stmt_cursor, ':found', found);
  dbms_sql.close_cursor(stmt_cursor);
  
  dbms_output.put_line('FOUND: '||found);
  
  IF found = 1 THEN
     stmt_cursor := dbms_sql.open_cursor;
     sys.dbms_sys_sql.parse_as_user(stmt_cursor, 'select machine from sys.x@'||p_obj, dbms_sql.NATIVE,uid);
     sys.dbms_sys_sql.define_column(stmt_cursor,1,strresult,1000);
     rc := dbms_sys_sql.execute(stmt_cursor);
     dbms_output.put_line('rc is: '||to_char(rc));
     loop
         if ( dbms_sys_sql.fetch_rows(stmt_cursor) > 0 )
         then
           dbms_sys_sql.column_value(stmt_cursor, 1,strresult );
           dbms_output.put_line('table_name is: '||strresult);
         else
          exit;     
         end if;     
     end loop;
     dbms_sql.close_cursor(stmt_cursor);
     DBMS_OUTPUT.PUT_LINE(stmt_cursor);
  END IF;
END;
/

1. Stop redo to standby
2. Set flashback off on standby
3. Set flashback on on standby
4. Start redo apply to standby

On standby

idle>select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                           .16                         0               4          0
BACKUP PIECE                           .01                         0               1          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                        46.95                         0              64          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

On Primary:
Use dgmgrl:
DGMGRL> connect sys@ORCL
Password:
Connected as SYSDBA.
DGMGRL> edit database orclstby set state='apply-off';
Succeeded.

On standby:
idle>ALTER DATABASE FLASHBACK OFF;

Database altered.

idle>alter database flashback on;

Database altered.

idle>select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                           .22                         0               7          0
BACKUP PIECE                           .01                         0               1          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                          1.5                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

On Primary:

DGMGRL> edit database orclstby set state='apply-on';
Succeeded.

The AUDIT_TRAIL needs to be purged.

Error may occur if USE_LAST_ARCH_TIMESTAMP => TRUE is used:
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 4425
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 825
ORA-06512: at line 2 
(Haven't investigated further) - maybe archive first is required or the archive timestamp has to be set.

Purge example:

SYS: ORCL> select segment_name||'   '||bytes/1024/1024 from dba_segments where owner = 'AUDSYS';

SEGMENT_NAME||''||BYTES/1024/1024
--------------------------------------------------------------------------------
SYS_LOB0000076986C00014$$   62.125
SYS_IL0000076986C00014$$   .0625
CLI_SCN$a48dc988$1$1   2
CLI_TIME$a48dc988$1$1   2
CLI_LOB$a48dc988$1$1   2
CLI_SWP$a48dc988$1$1   88

6 rows selected.

Elapsed: 00:00:00.09
SYS: ORCL> BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   USE_LAST_ARCH_TIMESTAMP => FALSE);
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.55
SYS: LASD1> select segment_name||'   '||bytes/1024/1024 from dba_segments where owner = 'AUDSYS';

SEGMENT_NAME||''||BYTES/1024/1024
--------------------------------------------------------------------------------
CLI_SWP$a48dc988$1$1   .0625
CLI_LOB$a48dc988$1$1   .0625
CLI_TIME$a48dc988$1$1   .0625
CLI_SCN$a48dc988$1$1   .0625
SYS_IL0000076986C00014$$   .0625
SYS_LOB0000076986C00014$$   .125

6 rows selected.

Elapsed: 00:00:00.10

Important initialization parameter to be determined before database creation.

select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET'

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

This will take into account subsequent sessions
- without the DEFERRED option, the command will fail

alter system set recyclebin=OFF DEFERRED SCOPE=BOTH;

------------------------------------------------------------------------
PURGE RECYCLEBIN;

To remove all dropped objects from the recyclebin (system wide):
PURGE DBA_RECYCLEBIN;

Drop a table without sending it to the recyclebin. Example:
DROP TABLE tab1 PURGE;