How to create the SCOTT user

To create the user SCOTT, run the script

$ORACLE_HOME/rdbms/admin/utlsampl.sql

Observe that user SCOTT will be created with the following statement:

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;

Note that if the default profile is using a password verification function, the creation will fail.

To work around, exchange default password "tiger" with something different that will honor the verification function, for example "T123G456R".

Remember to set

SET TERMOUT ON
SET ECHO ON

at the top, if you want to see the script output.

Changes in privilege "SELECT ANY DICTIONARY" in Oracle 12c

From version 12.1 and onwards, Oracle has introduced some changes to enhance security when granting the system privilege "SELECT ANY DICTIONARY".

In the New Features guide for version 12.1, the authors explain:

The SELECT ANY DICTIONARY privilege no longer permits access to security sensitive data dictionary tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS. This change increases the default security of the database by not allowing access to a subset of data dictionary tables through the SELECT ANY DICTIONARY privilege.

The access to USER$ has also been excempt when granting the system privilege SELECT ANY TABLE and the role SELECT_CATALOG_ROLE, but I have not yet found the documentation that verifies this.

How to use rman to create a backup-based clone on a remote server

It has been a while since I needed to use this technique, since we rely on storage snapshot clones these days. Sometimes though, a good old-fashioned rman clone based on backups is the only way to solve a problem.
Since I always avoid working on the production server during cloning, most steps are done logged onto the auxiliary server.

The method I used is based on "Backup-Based Duplication Without a Target Database and Recovery Catalog Connection"
Here is what I did:

1. On the production server, backup of the source database using RMAN:

rman target / nocatalog backup database plus archivelog;

From this point and onwards, everything is done while logged onto the destination server.

2. Get the files from the source server:

ssh testserver cd /u05 mkdir bup cd bup scp -r prodserver:/u05/flash_recovery_area/PRODDB01/2018_11_16/* .

3. Prepare the auxiliar pfile. Note that the parameters I use exceeds the actual required parameters.
But since the auxiliary instance will replace an already existing database instance, which is already tuned and has the correct memory parameters, I choose to include them. Notice also the db_file_name_convert and log_file_name_convert parameters. They control where rman will place the files during the restore process.

cd $ORACLE_HOME/dbs vi inittest1.ora Add the following: *.db_name='test1' *.db_unique_name='test1' *.audit_file_dest='/u01/oracle/admin/test1/adump' *.audit_trail='DB' *.compatible='12.2.0' *.control_files='/u02/oradata/test1/control01.ctl','/u04/fra/test1/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='proddb01','test1' *.log_file_name_convert='proddb01','test1' *.db_recovery_file_dest='/u05/flash_recovery_area/test1' *.db_recovery_file_dest_size=1000G *.diagnostic_dest='/u01/oracle' *.nls_language='NORWEGIAN' *.nls_territory='NORWAY' *.open_cursors=300 *.optimizer_adaptive_plans=FALSE *.optimizer_dynamic_sampling=0 *.optimizer_mode='ALL_ROWS' *.pga_aggregate_target=7222M *.processes=1500 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=21696M *.shared_pool_size=2624M *.streams_pool_size=256M *.undo_tablespace='UNDOTBS1' *.diagnostic_dest=/u01/oracle

4. I prefer using an spfile over a pfile. Therefore I take the time here to create an spfile:

sqlplus / as sysdba startup nomount pfile=inittest1.ora create spfile from pfile;

5. Startup the auxiliary instance in nomount-mode using the spfile:

shutdown abort startup nomount

6. Start the duplication process:

rman auxiliary / duplicate database to test1 backup location '/u05/fra/bup';

RMAN went through the normal restore, name switching and recovery phases. Finally, the database was opened with the resetlog option.

New parameter in dbms_redefinition.finish_redef_table in 12c

Oracle 12c adds a potentiall very useful feature to the dbms_redefinition package, in the procedure finish_redef_table: dml_lock_timeout

It specifies a time limit in seconds for how long a DML statment waits in a DML lock queue, before the procedure terminates gracefully.

By default, this parameter is set to 0 which means no waiting at all; the procedure will simply error out if it cannot get a lock on the table.

The maximumm value is 1000000 and will cause any DML statements to wait in perpetuity to aquire a DML lock.

Here is an example of its usage, where I have set the limit to 5 minutes (300 seconds):

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'SCOTT',
                                     orig_table=>'EMP',
                                     int_table=>'EMP_INTERIM', 
                                     dml_lock_timeout=>300);
END;
/

How do you configure an Oracle Golden Gate extract to run in Integrated Capture Mode?

There are different parameters valid for Golden Gate extracts, depending on which mode you choose (integrated mode or classical mode).

* Integrated extract parameters are found here
* Classical extract parameters are founnd here

Some of the parameters can be used in both configuration, while others are specific to one or the other.

An example of a parameter that is unique for Integrated Capture mode, is

TRANLOGOPTIONS INTEGRATEDPARAMS( option, ...)

You instruct the Golden Gate software to start your extract in Integrated mode by the following ggsci commands:

ADD EXTRACT MYEXT INTEGRATED TRANLOG, BEGIN NOW

To instruct the Golden Gate software to start the same extract in classical mode, alter the instructions slightly:

ADD EXTRACT MYEXT, TRANLOG, BEGIN NOW

Potential solution to Golden Gate error message "Parameter [INTEGRATEDPARAMS] is not valid for this configuration"

I was trying to set up an Integrated Capture in my Golden Gate environement, and kept getting the following error when starting my extract process:

OGG-10144  Oracle GoldenGate Capture for Oracle, myext.prm:  (myext.prm) line 3: Parameter [INTEGRATEDPARAMS] is not valid for this configuration.
ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, myext.prm:  PROCESS ABENDING.

In my case, this was caused by the fact that the extract was configured with classical capture, like this:

ADD EXTRACT MYEXT, TRANLOG, BEGIN NOW

The correct way to configure an extract in integrated capture mode is:

ADD EXTRACT MYEXT INTEGRATED TRANLOG, BEGIN NOW

How to lookup parameter definitions in Golden Gate 12.2

A potentially very useful feature when working with Golden Gate configurations is the ability to lookup parameter definition usage, with the new command

GGSCI (myserver.mydomain.com as ggadmin@testdb01) 31> info param tranlogoptions.INTEGRATEDPARAMS

param name  : tranlogoptions.integratedparams
description : (Oracle) Valid for Extract in integrated capture mode (Oracle Standard or Enterprise Edition 11.2.0.3 or later) 
Passes parameters and values to the Oracle database logmining server when Extract is in integrated capture mode.
argument    : no argument
options     : ANNOTATEDDLSIZE, CAPTURE_IDKEY_OBJECTS
              CAPTURE_SEQUENCE_NEXTVAL, DISABLE_ON_LIMIT
              DOWNSTREAM_REAL_TIME_MINE, ENABLE_PROCEDURAL_REPLICATION
              IGNORE_TRANSACTION, IGNORE_UNSUPPORTED_TABLE, INCLUDE_OBJECTS
              INLINE_LOB_OPTIMIZATION, INLINE_SFLOB_OPTIMIZATION, MAXIMUM_SCN
              MAX_SGA_SIZE, MERGE_THRESHOLD, MESSAGE_LIMIT
              MESSAGE_TRACKING_FREQUENCY, PARALLELISM
              SKIP_AUTOFILTERED_TABLE_DDL, SPLIT_THRESHOLD, STARTUP_SECONDS
              TIME_LIMIT, TRACE_LEVEL, USE_RAC_SERVICE, WRITE_ALERT_LOG
              XOUT_CLIENT_EXISTS
component(s): EXTRACT
mode(s)     : Integrated Extract
platform(s) : AIX
              HPUX-IT
              HPUX-PA
              Linux
              Solaris SPARC
              Solaris x86
              Windows x64
versions    :
    max ver : 12.3.0.1.85
database(s) : all supported databases (on the supported platforms).
status      : current
mandatory   : false
dynamic     : false
relations   : none

Source: http://www.oracle-scn.com/oracle-goldengate-12-2-new-feature-info-param/