KZN Tala Lodge Private Game Reserve

Tala Lodge Private Game Reserve is a reserve 45 minutes from Durban city. I was invited to attend a team building weekend for a company department I worked with in the past. I have been taking photos for the department for the past 4 years.

The lodge is situated on a 3000 hectare piece of land. You will find rhino, giraffe, wildebeest and a number of other animals. The staff are friendly and accommodating and the food is quality.
It is the perfect place to quickly escape the city owing to its short distance.

Below are a few pictures taken over the weekend.

Daily Drive Space Report

This post aims to setup an automatic space report email script for Windows. In Linux, the function df -m is used to display the amount of space on drives in clear readable manner.

/* Snippet taken from MOBAXTERM */
[2016-06-29 15:01.14]  /drives/c/mine/sql
[$$$$] ➤ df -m
Filesystem           1M-blocks      Used Available Use% Mounted on
C:/mine/home            304893    108067    196826  35% /home/mobaxterm
C:                      304893    108067    196826  35% /drives/c
J:                      215040    177142     37898  82% /drives/j
S:                      112635     87374     25261  78% /drives/s
U:                      572412    438530    133882  77% /drives/u
V:                      153597     22683    130914  15% /drives/v
W:                      153597     22683    130914  15% /drives/w
X:                      215040    177142     37898  82% /drives/x
Y:                      153597     22683    130914  15% /drives/y
Z:                      572412    438530    133882  77% /drives/z
                                                                                                                                                                                                        
───────────────────────────────────────────────────────────────────
[2016-06-30 08:57.54]  /drives/c/mine/sql
[$$$$] ➤

In Windows, there is no df -m nor does the UnxUtils df.exe seem to want to work as shown below.
C:\mine\Programs\unixutils\df -m
C:\mine\Programs\unixutils\df: cannot read table of mounted filesystems

To help keep tabs on a development environment and the disk space the backups consume, I've resorted a home made solution.

REQUIREMENTS:

1. Access to Task Scheduler
2. A from address from a recognized mail server within your domain.
3. A home folder to deploy the scripts

Step 1
Create the directories
mkdir E:\dba\script\backupfile_report

Step 2
Create the file backupfile_report.bat in the above working directory. Once created, load it with the following script:
SET WORKING_DIR=E:\dba\script\backupfile_report
SET PATH=%PATH%;%WORKING_DIR%
SET MONITOR_DIRECTORY=G:\RMAN_Backups

cd %WORKING_DIR%
E:

SETLOCAL EnableDelayedExpansion

REM change code page 1252 is used to remove the Unicode characters of the dir command output
chcp 1252

REM ^ is used to escape characters like greater or less than sybols from the output
REM The pre tag is used so that the email spaces the output at fixed spacing
echo ^<pre^> > %WORKING_DIR%\backupfile_report.log

echo FILES IN BACKUP DIR  >> %WORKING_DIR%\backupfile_report.log
echo ===================  >> %WORKING_DIR%\backupfile_report.log

dir /O:D %MONITOR_DIRECTORY% | find "7z" >> %WORKING_DIR%\backupfile_report.log

echo ^<br^> >> %WORKING_DIR%\backupfile_report.log
echo FREE SPACE >> %WORKING_DIR%\backupfile_report.log
echo ========== >> %WORKING_DIR%\backupfile_report.log
dir G:\ | find "bytes free"  >> %WORKING_DIR%\backupfile_report.log

echo ^</pre^> >> %WORKING_DIR%\backupfile_report.log

mailhtml.vbs <FROM_ADDRESS> <TO_ADDRESS> "<SERVER> Backup Location Report" %WORKING_DIR%\backupfile_report.log

Step 3
Create the emailing script mailhtml.vbs in the same directory as the batch file.
Load the file up with the following script.

'Grab the parameters
from_address = WScript.Arguments(0)
to_address = WScript.Arguments(1)
subject = WScript.Arguments(2)
file = WScript.Arguments(3)

'Set the file reading variables
Const ForReading = 1
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

'Set the mail properties
SMTPServer = "<SMTP_RELAY_SERVER>"
Recipient = to_address
From = from_address
Subject = subject


'Create the mail object
GenericSendmail SMTPserver, From, Recipient, Subject, Message
Sub GenericSendmail (SMTPserver, From, Recipient, Subject, Message)
Message = fso.OpenTextFile(file,ForReading).ReadAll

'Assisgn the values to our mail object and send the mail off
set msg = WScript.CreateObject("CDO.Message")
msg.From = From
msg.To = Recipient
msg.Subject = Subject
msg.HTMLBody = Message
msg.Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msg.Configuration.Fields.Update
msg.Send
End Sub

Step 4
Create the task scheduler for the batch script. To see how this is done, see Step 4 in the "Cluster Health Report" post. Alternatively, import the following task scheduler export:

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
  <RegistrationInfo>
    <Date>2016-06-29T14:36:45.1784573</Date>
    <Author>ADMIN</Author>
    <Description>This reports to us the G:\RMAN_Backup location as well as the space left on the G drive.</Description>
  </RegistrationInfo>
  <Triggers>
    <CalendarTrigger>
      <StartBoundary>2016-06-29T05:30:00</StartBoundary>
      <Enabled>true</Enabled>
      <ScheduleByDay>
        <DaysInterval>1</DaysInterval>
      </ScheduleByDay>
    </CalendarTrigger>
  </Triggers>
  <Principals>
    <Principal id="Author">
      <UserId>ADMIN</UserId>
      <LogonType>S4U</LogonType>
      <RunLevel>HighestAvailable</RunLevel>
    </Principal>
  </Principals>
  <Settings>
    <MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
    <DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
    <StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
    <AllowHardTerminate>true</AllowHardTerminate>
    <StartWhenAvailable>false</StartWhenAvailable>
    <RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
    <IdleSettings>
      <StopOnIdleEnd>true</StopOnIdleEnd>
      <RestartOnIdle>false</RestartOnIdle>
    </IdleSettings>
    <AllowStartOnDemand>true</AllowStartOnDemand>
    <Enabled>true</Enabled>
    <Hidden>false</Hidden>
    <RunOnlyIfIdle>false</RunOnlyIfIdle>
    <WakeToRun>false</WakeToRun>
    <ExecutionTimeLimit>P3D</ExecutionTimeLimit>
    <Priority>7</Priority>
  </Settings>
  <Actions Context="Author">
    <Exec>
      <Command>E:\dba\script\backupfile_report\backupfile_report.bat</Command>
      <WorkingDirectory>E:\dba\script\backupfile_report\</WorkingDirectory>
    </Exec>
  </Actions>
</Task>

Here is the contents of the email sent.

OUTPUT:

FILES IN BACKUP DIR  
===================  
2016/06/27  05:00 PM               694 DB1_2016_06_27.7z
2016/06/27  05:12 PM               566 DB2_2016_06_27.7z
2016/06/27  05:33 PM            13 397 DB3_2016_06_27.7z
2016/06/27  05:38 PM       897 066 419 DB4_2016_06_27.7z
2016/06/27  05:38 PM               694 DB5_2016_06_27.7z
2016/06/27  05:47 PM     1 728 202 632 DB6_2016_06_27.7z
2016/06/27  05:56 PM     1 216 270 881 DB7_2016_06_27.7z
2016/06/27  06:06 PM     1 927 492 151 DB8_2016_06_27.7z
2016/06/27  06:14 PM     1 733 744 322 DB9_2016_06_27.7z
2016/06/28  05:00 PM               694 DB1_2016_06_28.7z
2016/06/28  05:24 PM               566 DB2_2016_06_28.7z
2016/06/28  05:54 PM            13 397 DB3_2016_06_28.7z
2016/06/28  05:58 PM       892 120 199 DB4_2016_06_28.7z
2016/06/28  05:58 PM               694 DB5_2016_06_28.7z
2016/06/28  06:07 PM     1 720 066 139 DB6_2016_06_28.7z
2016/06/28  06:16 PM     1 213 519 663 DB7_2016_06_28.7z
2016/06/28  06:39 PM     1 906 291 500 DB8_2016_06_28.7z
2016/06/28  06:54 PM     1 721 172 646 DB9_2016_06_28.7z
2016/06/29  05:00 PM               694 DB1_2016_06_29.7z
2016/06/29  06:10 PM    13 577 939 773 DB2_2016_06_29.7z
2016/06/29  07:00 PM    10 541 191 641 DB3_2016_06_29.7z
2016/06/29  07:16 PM       857 023 418 DB4_2016_06_29.7z
2016/06/29  07:29 PM               694 DB5_2016_06_29.7z
2016/06/29  08:13 PM     1 666 050 028 DB6_2016_06_29.7z
2016/06/29  08:49 PM     1 160 641 332 DB7_2016_06_29.7z
2016/06/29  09:16 PM     1 878 692 682 DB8_2016_06_29.7z
2016/06/29  09:43 PM     1 720 452 716 DB9_2016_06_29.7z
2016/06/29  10:18 PM     5 990 226 449 DB0_2016_06_29.7z


FREE SPACE 
========== 
              13 Dir(s)  48 683 343 872 bytes free

Upgrading Apex from 4.0.2 to 4.2 in Oracle XE

This post features the steps to upgrade Apex 4.0.2 to 4.2.2 in a Windows XE database.
The process of upgrading apex is pretty standard so this guide can apply a few versions of Apex.

OVERVIEW:
1. Create the APEX_DATA tablespace
2. Extract the APEX install files into a working directory
3. Shutdown the APEX gateway listener
4. Run the installation file
5. Check the registry and post installation status
6. Startup the APEX gateway listener

1. Create the APEX_DATA tablespace

CREATE TABLESPACE apex_data DATAFILE 'C:\ORACLE\APP\ORACLE\ORADATA\XE\APEX_DATA.DBF' SIZE 2G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

2. Shutdown the APEX gateway listener
EXEC DBMS_XDB.sethttpport(0);

3. Extract the APEX install files into a working directory

I’ve moved the old $ORACLE_HOME/apex directory aside into $ORACLE_HOME/apex.4.0.2
I’ve extracted the 4.2.2 zip file into the $ORACLE_HOME/apex directory

4. Run the installation file

In cmd, change directory to your apex directory:
C:\oracle\app\oracle\product\11.2.0\server\apex

Log in to sqlplus as sysdba
sqlplus /nolog
conn / as sysdba

Check the current apex version in the database
SQL> select comp_name, version, status from dba_registry where comp_name = 'Oracle Application Express';

COMP_NAME                    VERSION          STATUS
---------------------------- ---------------- ------------------
Oracle Application Express   4.0.2            VALID

Run the apex installer
SQL> @apexins.sql APEX_DATA APEX_DATA TEMP /i/
.  ____   ____           ____        ____
. /    \ |    \   /\    /     |     /
.|      ||    /  /  \  |      |    |
.|      ||---    ----  |      |    |-- 
.|      ||   \  /    \ |      |    |
. \____/ |    \/      \ \____ |____ \____
.
. Application Express (APEX) Installation.
..........................................
.
... Checking prerequisites (MANUAL)
.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

.
... Prerequisite checks passed.
.

Performing Application Express component validation - please wait...

old   6:     if '^UPGRADE' = '1' then
new   6:     if '2' = '1' then
old   7:        dbms_registry.loaded('APEX','^version');
new   7:        dbms_registry.loaded('APEX','4.2.2.00.11');
old   8:     elsif '^UPGRADE' = '2' then
new   8:     elsif '2' = '2' then
old  10:             dbms_registry.upgraded('APEX','^version');
new  10:             dbms_registry.upgraded('APEX','4.2.2.00.11');
old  12:             dbms_registry.loaded('APEX','^version');
new  12:             dbms_registry.loaded('APEX','4.2.2.00.11');
Completing registration process. 08:59:08
Validating installation.  08:59:08
...Database user "SYS", database schema "APEX_040200", user# "49" 08:59:08
...Compiled 0 out of 3004 objects considered, 0 failed compilation 08:59:09
...266 packages
...258 package bodies
...452 tables
...11 functions
...16 procedures
...3 sequences
...457 triggers
...1320 indexes
...211 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 08:59:09
...Completed key object existence check 08:59:09
...Setting DBMS Registry 08:59:09
...Setting DBMS Registry Complete 08:59:09
...Exiting validate 08:59:09

PL/SQL procedure successfully completed.

timing for: Validate Installation
Elapsed: 00:00:00.53
old   1: alter session set current_schema = ^APPUN
new   1: alter session set current_schema = APEX_040200

Session altered.

timing for: Complete Installation
Elapsed: 00:18:40.58

PL/SQL procedure successfully completed.


GLOBAL_NAME
------------------------------
SYS

1 row selected.

The apex installer will run for about 10-20 minutes while it reinstalls its libraries.

It will boot you out once it has completed.

5. Check the registry and post installation status
SQL> select comp_name, version, status from dba_registry where comp_name = 'Oracle Application Express';

COMP_NAME                      VERSION                        STATUS
------------------------------ ------------------------------ ---------------
Oracle Application Express     4.2.2.00.11                    VALID

SQL>

6. Startup the APEX gateway listener
EXEC DBMS_XDB.sethttpport(8080);

Done

Setting up a standalone APEX Listener in Glassfish

I setup Glass fish on a development box a few weeks ago. My understanding of how Glassfish works and where it fits into the enterprise architecture picture is limited but I believe I have an idea of what it is: Glassfish is a free application server. It looks and feels, at least to me, like a lightweight Oracle 11g Middleware. The reason why Glassfish was needed is that the development team wanted to setup an APEX listener and that listener needed to be accessible to the outside world and hosted outside of the database. The database that is at the centre of this setup has APEX 4.2 installed in it.

My notes for setting up APEX are below.

1. Download Glassfish and the APEX Standalone Listener:

Glassfish server can be found here:
https://glassfish.java.net/download.html

Listener can be found here:
http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/apex-listener-download-v205-2135981.html

*NOTE: The download locations can change from time to time, so for this post I used Apex Listener 2.0.5
I later learned that Oracle rebranded the listener to Oracle Restful Services. This note is focused on the old Apex Listener.

2. Install
Unzip Glassfish in a suitable location. I use E:\oracle\glassfish4. Glassfish works out the box so there is no installation required.

3. Start the default domain

E:\oracle\glassfish4\bin>asadmin start-domain
Waiting for domain1 to start ..................
Successfully started the domain : domain1
domain  Location: E:\oracle\glassfish4\glassfish\domains\domain1
Log File: E:\oracle\glassfish4\glassfish\domains\domain1\logs\server.log
Admin Port: 4848
Command start-domain executed successfully.

4. Set the admin password 
For now, I use "adminadmin"

E:\oracle\glassfish4\bin>asadmin --host localhost --port 4848 change-admin-password
Enter admin user name [default: admin]>admin
Enter the admin password> <TYPE ADMINADMIN>
Enter the new admin password> <TYPE ADMINADMIN>
Enter the new admin password again> <TYPE ADMINADMIN>
Command change-admin-password executed successfully.

5. Enable secure admin
E:\oracle\glassfish4\bin>asadmin --host localhost --port 4848 enable-secure-admin
Enter admin user name>  admin
Enter admin password for user "admin"> <TYPE ADMINADMIN>
You must restart all running servers for the change in secure admin to take effect.
Command enable-secure-admin executed successfully.

6. Setup the APEX.WAR file for glassfish.
For Glassfish to present an Apex Listener, all it needs is the APEX.WAR file. I navigate to the location of the WAR file located inside the unzipped listener folder.
I then configure the file and save it. I configure the listener to use directory E:\oracle\glassfish4\glassfish\domains\domain1\apex_config to contain its configuration information.
So, make the directory E:\oracle\glassfish4\glassfish\domains\domain1\apex_config before configuring the WAR file.

G:\apex_listener_2_0_5>java -jar apex.war configdir E:\oracle\glassfish4\glassfish\domains\domain1\apex_config
Apr 21, 2016 3:30:10 PM oracle.dbtools.common.config.cmds.ConfigDir execute
INFO: Set config.dir to E:\oracle\glassfish4\glassfish\domains\domain1\apex_config in: G:\apex_listener_2_0_5\apex.war

7. Configure the listener to point to one database
G:\apex_listener_2_0_5>java -jar apex.war setup
Apr 21, 2016 3:30:43 PM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder
INFO: Using configuration folder: E:\oracle\glassfish4\glassfish\domains\domain1\apex_config\apex
Enter the name of the database server [localhost]:<GLASSFISH SERVER>
Enter the database listen port [1521]:1522
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:<DATABASE SID>
Enter the database user name [APEX_PUBLIC_USER]:APEX_PUBLIC_USER
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to enter passwords for the RESTful Services database users (APEX_LISTENER,APEX_REST_PUBLIC_USER), 2 to use the same password as used for APEX_PUBLIC_USE
R or, 3 to skip this step [1]:1
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Apr 21, 2016 3:32:09 PM oracle.dbtools.common.config.file.ConfigurationFiles update
INFO: Updated configurations: defaults, apex, apex_al, apex_rt

8. Make the apex images directory available to the APEX.WAR file
These images are the ones found within the APEX database installation package.
On our development server, our /i alias is set to e:\apex422_images. So, I setup the war to point to the same location.

G:\apex_listener_2_0_5>java -jar apex.war static e:\apex422_images
WAR Generation complete
WAR location     : G:\apex_listener_2_0_5\i.war
Context path     : /i
Static resources : e:\apex422_images
Ensure the static resources are available at path: e:\apex422_images
on the server where the WAR is deployed

9. Upload the JAR file into glass fish.
Navigate to https://<GLASSFISH HOSTING SERVER>:4848

Enter the credentials selected earlier.
Once logged in, click the "Applications" link on the left. Once it opens in the right panel, click the "Deploy" button. You will presented with the screen below.

Click the "Choose File" button and locate the WAR. Once selected, click "OK"

This will load the JAR file into the Glassfish framework.

9. Stop and start the domain
E:\oracle\glassfish4\bin>asadmin stop-domain
Waiting for the domain to stop .
Command stop-domain executed successfully.

E:\oracle\glassfish4\bin>asadmin start-domain
Waiting for domain1 to start ...........................................
Successfully started the domain : domain1
domain  Location: E:\oracle\glassfish4\glassfish\domains\domain1
Log File: E:\oracle\glassfish4\glassfish\domains\domain1\logs\server.log
Admin Port: 4848
Command start-domain executed successfully.

10. Test glass fish and the new glasshfish administration page.

It seems that Glassfish piggy backs off port 8080 - the other webserver port when 80 is taken.
The glassfish administration console presents itself on port 4848.
To logon to the Glassfish administration console, use the credentials supplied in the APEX setup.

Test Apex

http://<GLASSFISH HOSTING SERVER>:8080/apex/apex_admin

Duplicate Database in Windows

A long time ago I put together notes on database duplication in Linux. Today I am posting more comprehensive notes for duplicating a database within a Windows environment.

The database I am cloning to (auxiliary) does not exist yet. The source database is production database (target) and I have copies of its most recent backup. The backup is a warm backup which I've copied from the server and placed in a location which which the duplication will call upon in the duplication process.

The destination environment has a listener which all development database register with so there are no details on setting up the listener.

This note is comprised of the following steps:
1. Create directories for auxiliary.
2. Create the pfile for auxiliary.
3. Create the service for auxiliary.
4. Create the spfile for auxiliary.
5. Adjust auxiliary so that it does not run production jobs upon opening
6. Duplicate the target to auxiliary using a recent warm backup from production
   -Specify the target and destination
   -Specify the databse file name conversion parameters
   -Specify the source backup location from which to duplicate from
   -Specify the log group
7. Disable auxiliary jobs
8. Adjust auxiliary to allow for the running of jobs after disabling the production jobs

Below are the descriptions of the <TAGS> found through the note:
<AUXILIARY_INSTANCE> - This is the name of the destination I want to restore a database into
<TARGET_INSTANCE> - This is the name of the database contained within the backup files used in the duplication process
<TARGET_INSTANCE_FOLDER> - This is the folder which holds my backup files used in the duplication process
<CONDITIONS FOR JOBS I WANT TO DISABLE> - This is a substitute for the conditions of which jobs I want to disable after the duplication. It is quite unlikely that jobs within a production database are meant to be running in a non-production environment.

1. Create directories for auxiliary.
The database will make use of the following locations.

REM Place for audits
mkdir -p E:\Oracle\admin\<AUXILIARY_INSTANCE>\adump
REM Place for datafiles
mkdir F:\Oracle\oradata\<AUXILIARY_INSTANCE>
REM Place for archivelogs
mkdir G:\Oracle\oradata\<AUXILIARY_INSTANCE>\archive

2. Create the pfile for auxiliary.
In command, open up notepad with the following command and paste the parameters into the file and save. Adjust accordingly.

notepad E:\oracle\product\11.2.0\dbhome_1\database\init<AUXILIARY_INSTANCE>.ora

*.audit_file_dest='E:\Oracle\admin\<AUXILIARY_INSTANCE>\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL01.CTL','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL02.CTL','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL03.CTL'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='<AUXILIARY_INSTANCE>'#Reset to original value by RMAN
*.diagnostic_dest='E:\Oracle'
*.dispatchers='(protocol=TCP)'
*.fixed_date='none'
*.job_queue_processes=0
*.local_listener='LISTENER_LOCAL'
*.log_archive_dest_1='LOCATION=g:\oracle\oradata\<AUXILIARY_INSTANCE>\archive'
*.log_file_name_convert='F:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>'
*.log_archive_format='ARC%S_%R.%T'
*.memory_target=1063256064
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.shared_servers=2
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='*'

*NOTE: The destination machine has a listener titled "LISTENER_LOCAL" that services all the instance on the machine. This is what *.LOCAL_LISTENER is all about.
This tells the AUXILIARY database "When you startup - I want you to register yourself with the listener on this machine titled LISTENER_LOCAL". If the environment doesn't have a common listener which instance can register with, then a static entry must be made in the listener.ora file for our new database - I will not cover this here.

*NOTE: Also note that you should have the *.log_file_name_convert parameter in your parameter list, especially if the target and auxiliary are on the same server. Should db_file_name_convert be omitted in both parameter and RMAN script, RMAN will restore the BK files as was and try to write the datafiles back to the location of the source. Datafiles about to be overwritten will halt the duplication process and RMAN will warn you that the destination files are in use by another instance, but this is not the case for redolog files. Duplicate database has, for me in the past, accidentally overwritten the source database redologs if one plays fast and loose with the details of the log file name conversion parameter - the duplicate command will default to the target databases log file location during control file creation if an administrator has not specified log conversion in some form or another.

3. Create the service for auxiliary.
I create the Windows service to represent the new instance.

set ORACLE_SID=<AUXILIARY_INSTANCE>

set ORACLE_HOME=E:\oracle\product\11.2.0\dbhome_1

oradim -new -SID <AUXILIARY_INSTANCE> -startmode manual -pfile 'E:\oracle\product\11.2.0\dbhome_1\database\init<AUXILIARY_INSTANCE>.ora'

OUTPUT
C:\Windows\system32>set ORACLE_SID=<AUXILIARY_INSTANCE>
C:\Windows\system32>set ORACLE_HOME=E:\oracle\product\11.2.0\dbhome_1
C:\Windows\system32>oradim -new -SID <AUXILIARY_INSTANCE> -startmode manual -pfile 'E:\oracle\product\11.2.0\dbhome_1\database\init<AUXILIARY_INSTANCE>.ora'
Instance created.

4. Create the spfile for auxiliary.
Create an spfile from the pfile used to start the database service.

create spfile from pfile;

OUTPUT
C:\Windows\system32>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 17 08:57:50 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;

File created.

SQL>

5. Adjust auxiliary so that it does not run production jobs upon opening in the development environment

alter system set job_queue_processes=0 scope=spfile;

OUTPUT
C:\Windows\system32>sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 17 09:06:20 2016

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size                  2261960 bytes
Variable Size             612371512 bytes
Database Buffers          440401920 bytes
Redo Buffers                5550080 bytes
SQL> alter system set job_queue_processes=0 scope=spfile;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

C:\Windows\system32>


6. Duplicate the target to auxiliary using a recent warm backup from production
This is the part where the disks are put to work.
Because I am not duplicating directly from the target database but rather duplicating from a backup (datafiles, control files, archivelogfiles), I do not specify the TARGET parameter when connecting to RMAN. I also do not specify NOCATALOG because RMAN will default to NOCATALOG mode when catalog information is not specific in the RMAN connection command.

DB_FILE_NAME_CONVERT is used to automatically rename the restored backup files to a new location: considering the auxiliary environment is NOT the production environment and the drives are not the same, I have provided the convert to suit the destination environments layout.

BACKUP LOCATION is a neat little parameter that tells RMAN where to look for the backup files of TARGET. This does away with the need to CATALOG the backup pieces prior to duplication.

LOGFILE tells RMAN where to create the redo logfiles and their sizes.

rman auxiliary / 
run {
    DUPLICATE DATABASE <TARGET_DATABASE> to "<AUXILIARY_INSTANCE>" 
    DB_FILE_NAME_CONVERT='G:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','H:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','E:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','D:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','F:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>' 
    BACKUP LOCATION='C:\BACKUP\<TARGET_INSTANCE_FOLDER>' 
    LOGFILE 
      'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo01.log' size 10M, 
      'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo02.log' size 10M, 
      'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo03.log' size 10M; 
    }
exit 

OUTPUT

RMAN> run {
2>     DUPLICATE DATABASE <TARGET_DATABASE> to "<AUXILIARY_INSTANCE>"
3>     DB_FILE_NAME_CONVERT='G:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','H:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','E:\ORACLE\ORADATA\G
DATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','F:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>'
4>     BACKUP LOCATION='C:\BACKUP\<TARGET_INSTANCE_FOLDER>'
5>     LOGFILE
6>       'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo01.log' size 10M,
7>       'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo02.log' size 10M,
8>       'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo03.log' size 10M;
9>      }

Starting Duplicate Db at 17-JUN-16

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''<TARGET_DATABASE>'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''<AUXILIARY_INSTANCE>'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  'C:\BACKUP\<TARGET_INSTANCE_FOLDER>\<TARGET_DATABASE>_BRR7VP4C_1_1.RMAN';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''<TARGET_DATABASE>'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''<AUXILIARY_INSTANCE>'' comment= ''Modified by RMAN duplicate'' scope=spfile
executing Memory Script

sql statement: alter system set  db_name =  ''<TARGET_DATABASE>'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''<AUXILIARY_INSTANCE>'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1060585472 bytes

Fixed Size                     2261960 bytes
Variable Size                612371512 bytes
Database Buffers             440401920 bytes
Redo Buffers                   5550080 bytes

Starting restore at 17-JUN-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL01.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL02.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL03.CTL
Finished restore at 17-JUN-16

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 device type=DISK
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL01.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL02.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL03.CTL
Finished restore at 17-JUN-16

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 device type=DISK

........................................................

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\USERS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00005 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FINDATADM1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00006 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FINDATAUDIT1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00007 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FINDATINT1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00008 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FININDADM1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00009 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FININDINT1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00010 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\AUDIT_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00011 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATADMD1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00012 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATADMD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00013 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATADMS_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00014 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINTERFACE1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00015 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATOPSD1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00016 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATOPSD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00017 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATOPSS_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00018 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDADMD1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00019 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDADMD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00020 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDADMS_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00021 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDINTERFACE1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00022 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD1_1.DBF
channel ORA_AUX_DISK_1: restoring datafile 00023 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00024 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSS.DBF
channel ORA_AUX_DISK_1: restoring datafile 00025 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPUSERS1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00026 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\UNDOTBS02.DBF
channel ORA_AUX_DISK_1: reading from backup piece C:\BACKUP\<TARGET_INSTANCE_FOLDER>\<TARGET_DATABASE>_BQR7VNJU_1_1.RMAN
......................
input datafile copy RECID=20 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDINTERFACE1_01.DBF
datafile 22 switched to datafile copy
input datafile copy RECID=21 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD1_1.DBF
datafile 23 switched to datafile copy
input datafile copy RECID=22 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD2_01.DBF
datafile 24 switched to datafile copy
input datafile copy RECID=23 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSS.DBF
datafile 25 switched to datafile copy
input datafile copy RECID=24 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPUSERS1_01.DBF
datafile 26 switched to datafile copy
input datafile copy RECID=25 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\UNDOTBS02.DBF

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 17-JUN-16

RMAN>

While the database is restoring, I can monitor the duplication progress using v$session_longops using another database session. The RMAN process registers its block restoration effort in the v$session_longops dynamic view. How handy is that. In another cmd window, I run the following connected to the auxiliary instance

select message from v$session_longops;

OUTPUT
E:\oracle\glassfish4\bin>set ORACLE_SID=<AUXILIARY_INSTANCE>

E:\oracle\glassfish4\bin>sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 17 09:43:06 2016

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

SQL> conn / as sysdba
Connected.
SQL> select message from v$session_longops;

MESSAGE
--------------------------------------------------------------------------------
RMAN: aggregate input: restore 421: 4915871 out of 0 Blocks done
RMAN: full datafile restore: Set Count 11642: 5212774 out of 5744384 Blocks done

SQL> /

MESSAGE
--------------------------------------------------------------------------------
RMAN: aggregate input: restore 421: 5260134 out of 0 Blocks done
RMAN: full datafile restore: Set Count 11642: 5306598 out of 5744384 Blocks done

SQL> /

MESSAGE
--------------------------------------------------------------------------------
RMAN: aggregate input: restore 421: 5260134 out of 0 Blocks done
RMAN: full datafile restore: Set Count 11642: 5319142 out of 5744384 Blocks done

SQL>

See how the block count increases, this is my proverbial progress bar for observation

7. Disable auxiliary jobs
Here I disable the production jobs before re-enabling the job parameter.

conn / as sysdba
BEGIN
  FOR r_jobs IN (SELECT job 
                   FROM dba_jobs 
                  WHERE <CONDITIONS FOR JOBS I WANT TO DISABLE>
                  )
  LOOP
    DBMS_JOB.BROKEN(r_jobs.job,TRUE);
  END LOOP;
  
  FOR r_scheduler_jobs IN (SELECT owner,
                                  job_name 
                             FROM SYS.dba_scheduler_jobs 
                            WHERE  <CONDITIONS FOR JOBS I WANT TO DISABLE>
                           )
  LOOP
    DBMS_SCHEDULER.DISABLE('"' || r_scheduler_jobs.owner  || '"."' || r_scheduler_jobs.job_name || '"',TRUE);
  END LOOP;

END;
/

OUTPUT
SQL> BEGIN
  2    FOR r_jobs IN (SELECT job
  3                     FROM dba_jobs
  4                    WHERE <CONDITIONS FOR JOBS I WANT TO DISABLE>
  5                    )
  6    LOOP
  7      DBMS_JOB.BROKEN(r_jobs.job,TRUE);
  8    END LOOP;
  9
 10    FOR r_scheduler_jobs IN (SELECT owner,
 11                                    job_name
 12                               FROM SYS.dba_scheduler_jobs
 13                              WHERE <CONDITIONS FOR JOBS I WANT TO DISABLE>
 14                             )
 15    LOOP
 16      DBMS_SCHEDULER.DISABLE('"' || r_scheduler_jobs.owner  || '"."' || r_scheduler_jobs.job_name || '"',TRUE);
 17    END LOOP;
 18
 19  END;
 20  /

PL/SQL procedure successfully completed.

SQL> select owner, job_name, enabled from dba_scheduler_jobs where enabled = 'TRUE';

OWNER                          JOB_NAME                       ENABL
------------------------------ ------------------------------ -----
SYS                            PURGE_LOG                      TRUE
SYS                            ORA$AUTOTASK_CLEAN             TRUE
SYS                            DRA_REEVALUATE_OPEN_FAILURES   TRUE
ORACLE_OCM                     MGMT_CONFIG_JOB                TRUE
ORACLE_OCM                     MGMT_STATS_CONFIG_JOB          TRUE
SYS                            BSLN_MAINTAIN_STATS_JOB        TRUE
SYS                            RSE$CLEAN_RECOVERABLE_SCRIPT   TRUE
SYS                            SM$CLEAN_AUTO_SPLIT_MERGE      TRUE
EXFSYS                         RLM$EVTCLEANUP                 TRUE
EXFSYS                         RLM$SCHDNEGACTION              TRUE
SYS                            AUDIT_PURGE                    TRUE

11 rows selected.

SQL>

Only the system jobs are left running.

8. Adjust auxiliary to allow for the runnign of jobs after disabling the production jobs
Now that the jobs are disabled, I can go ahead and allow the system to run jobs again.

alter system set job_queue_processes=5 scope=both;

OUTPUT
SQL> alter system set job_queue_processes=5 scope=both;

System altered.

SQL>

All done.