REP-51002

when Accessing Reports Server, but it is up and Ready per Trace Files 
Symptom
Trying to access the Reports Server gives following error:
     REP-51002: Bind to Reports Server failed
This error is given when you try to access Reports Server using getserverinfo or showjobs.  For example:
     http://or11/reports/rwservlet/getserverinfo?server=rep_dev
     or
     http://or1/reports/rwservlet/showjobs?server=rep_dev

Also, running the command "rwdiag.sh -findAll" gives error:
     REP-50503 No server found in the network
However, in the Reports Server trace files (for example D:\Oracle10GASSuite\reports\logs\rep_dev), it shows that the Reports Server is actually up and running.  There are no errors in the trace files. 
Cause
This is a firewall permissions problem. 
It is not a problem with the Reports Server, as the server traces show it is up and running.

Solution

To implement the solution, please execute the following steps:

1. Open the port on the firewall to allow the Reports Server to be accessible.
a) Run command:
rwdiag.sh -findAll
b) From output, check what value is used for Channel port. For example:
Broadcast mechanism used to locate servers
Channel address = ###.###.###.###
Channel port = 14021
c) Open the channel port on the firewall, in this case, it is port 14021.
2. In the firewall, authorize requests from your host's IP address to ###.###.###.### (the channel address shown above).
Note:   If the above still does not work, then you may try adding the name of the machine where Reports Server is and the IP for that machine to the hosts file (/etc/hosts or C:\WINDOWS\system32\drivers\etc\hosts). For example:

Ref:  415407.1

AUTONOMOUS_TRANSACTION Pragma

One of the questioned asked today about the limitation of autonomous_trasaction pragma (10gR2). 
PRAGMA
Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They pass information to the compiler.
You can apply this pragma to:

Top-level (not nested) anonymous PL/SQL blocks
 Local, standalone, and packaged functions and procedures
Methods of a SQL object type
Database triggers

You cannot apply this pragma to an entire package or an entire an object type. Instead, you can apply the pragma to each packaged subprogram or object method.
You can code the pragma anywhere in the declarative section. For readability, code the pragma at the top of the section.
Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back.

Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK, and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.

In the main transaction, rolling back to a savepoint located before the call to the autonomous subprogram does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction.

If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.

If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.

Example:

CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE, 
                         new_sal NUMBER(8,2), old_sal NUMBER(8,2) );

CREATE OR REPLACE TRIGGER audit_sal
   AFTER UPDATE OF salary ON employees FOR EACH ROW
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- bind variables are used here for values
   INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE, 
                                 :new.salary, :old.salary );
  COMMIT;
END;

/

TNS-12535 / ORA-12535 on Connection to Database

1-

Symptoms

Oracle Net connections to database fail with error code TNS-12535 / ORA-12535: TNS:operation timed out. Oracle Net client trace can show large delay upon resolving TNS listener host

Cause
Operating system error connection timed out. The TNS listener is unable to service the requests quickly enough. The TNS listener has a queue (backlog) of connection requests that it needs to service. If it is unable to service these quick enough, they will start to timeout.

Solution

The size of the TNS listener queue can be influenced by setting parameter QUEUESIZE in the listener.ora file.   To specify a queue size for a listener, enter a value to the QUEUESIZE parameter at the end of any listening address:

LISTENER =
  (ADDRESS =(PROTOCOL = TCP)(HOST=SAMPLE.COM)(PORT = 1521)(QUEUESIZE=99)) 

Default LISTENER.ORA queue sizes

Solaris default QUEUESIZE is 5
Aix default QUEUESIZE is 1024
Linux Default QUEUESIZE is 1024
Windows default QUEUESIZE is 50

Ref: 214122.1

 

2-

Brief notes:

- In essence, the ORA-12535/TNS-12535 is a timing issue between the client and server.
- The TNS-12535 or ORA-12535 error is commonly a timeout error associated with Firewalls or slow Networks.
- It can also be due to an incorrect "timeout" parameter setting for the following files:
listener.ora -->
CONNECT_TIMEOUT_ (8.1.x and lower only)
or
INBOUND_CONNECT_TIMEOUT_ (9.2 and above)
sqlnet.ora -->
SQLNET.INBOUND_CONNECT_TIMEOUT (9.2 and up). 

Ref: 125351.1

3-  TNSPING command fails with the TNS-12535 error:

Cause 

Personal Firewall enabled on Client system.

Solution

To implement the solution, please execute the following steps:

Add the Oracle Client side applications to the Windows Firewall Exception List.

Click Start, Control Panel, and double click 'Windows Firewall'  Next, select the 'Exceptions Tab'
Use the 'Add Program...' dialog to add the programs to the Exception List.
If unable to navigate to the 'Windows Firewall' configurations window, you may be able to manually access the Window Firewall in Windows XP SP2 window by:
Clicking Start, click Run, type Firewall.cpl, and then click OK  Then from there you can edit the programs from the Exceptions Tab.

Ref: 341216.1


4-  Connect as SYSDBA Fails with TNS-12535 ,Can not start database

cause: Log files were being written from the ORASRV_BEQ_.COM file and the 32k version limit had been reached

fix:
Delete the log files.

Live Reporting with Data Pump -GG

Please review Live Reporting with OGG first.

We can add a data pump to add storage flexibility and to offload the overhead of filtering and conversion processing from the source system. In this configuration, the primary Extract writes to a local data pump and trail, and then the data pump sends the data to a remote trail . A data pump on the intermediary system reads the trail and moves the data to a remote trail on the target, which is read by a Replicat group.
The data pump on the source system is optional, but will help to protect against data loss in the event of a network outage.

Source System
 Configure Primary Extract
GGSCI (HOME-03-DB12) 31> ADD EXTRACT EXTPRRPT, tranlog, begin now
EXTRACT added.

GGSCI (HOME-03-DB12) 68> ADD EXTTRAIL D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirdat\lt, EXTRACT EXTPRRPT
EXTTRAIL added.
This is the local trail on the source system where the Extract process will write to and which is then read by the Data Pump process. We link this local trail to the Primary Extract group

-- Create the parameter file for primary Extract group.
GGSCI (HOME-03-DB12) 31> edit params extrptpr
-- Identify the Extract group:
EXTRACT extrptpr
-- Specify database login information as needed for the database (source):
USERID ggs_owner@homedev, PASSWORD ggs_owner;
-- Specify the local trail on the source system:
EXTTRAIL D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirdat\lt
--Specify transaction log options
TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY W:\DEST_1;
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT HOMEDEV_%S_%R.%T.ARC;
TRANLOGOPTIONS PATHMAP D:\ORACLE\PRODUCT\10.2.0\ORADATA\HOMEDEV\HOMEDEV U:\HOMEDEV\HOMEDEV
-- Specify tables to be captured:
TABLE scott.emp; -- you can put the scott.* if you want to include all the tables.


Configure data pump
Add EXTRACT for data pump. Use EXTTRAILSOURCE as the data source option, and specify the name of the local trail. 
GGSCI (HOME-03-DB12) 37> ADD EXTRACT extdpump, EXTTRAILSOURCE D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirdat\lt, BEGIN now
EXTRACT added.

Use the ADD RMTTRAIL command to specify a remote trail that will be created on remote or  the intermediary system. Use the EXTRACT argument to link the remote trail to the extdpump data pump group. The linked data pump writes to this trail.

GGSCI (HOME-03-DB12) 38> ADD RMTTRAIL Y:\dirdat\rt, EXTRACT extdpump
RMTTRAIL added.
GGSCI (HOME-03-DB12) 39> edit params extdpump
EXTRACT extdpump
USERID ggs_owner@homedev, PASSWORD ggs_owner
RMTHOST HOME-03-DB12, MGRPORT 7809;
--RMTTRAIL to specify the location of the remote trail and associate the same with the Data Pump group as --it will be wriiten to over the network by the data pump process
RMTTRAIL Y:\dirdat\rt;
PASSTHRU
TABLE scott.emp;To use PASSTHRU mode, the names of the source and target objects must be identical. No column mapping, filtering, SQLEXEC functions, transformation, or other functions that require data manipulation can be specified in the parameter file.

Target System
On target system add the replicat group.
GGSCI (HOME-03-DB12) 30> ADD REPLICAT reprptdp, EXTTRAIL Y:\dirdat\rt, begin now
REPLICAT added.
Edit parameter for replicat group
GGSCI (HOME-03-DB12) 31> edit params reprptdp
REPLICAT reprptdp
ASSUMETARGETDEFS
USERID ggs_owner@asmdb, PASSWORD ggs_owner
MAP scott.EMP, TARGET scott.EMP;

Source System
GGSCI (HOME-03-DB12) 40> start extrptdp
Sending START request to MANAGER ...
EXTRACT EXTRPTDP starting
GGSCI (HOME-03-DB12) 41> start extdpump
Sending START request to MANAGER ...
EXTRACT EXTDPUMP starting

Target System
GGSCI (HOME-03-DB12) 10> start replicat reprptdp

Now change some data on source (EMP) and observe the change on target


NOTE:I've used the same machine (Win200364bit) for source and target system. I emulated this as having two different folders for GG source and target.
 

Live Reporting with OGG

Reporting configuration:

Source system

To configure the Manager process
Step 1: On the source, configure the Manager process according to the instructions in tutorial

Step 2: On the source configure the Extract group, use the ADD EXTRACT command to create an Extract group.

GGSCI (HOME-03-DB12) 18> ADD EXTRACT extrpt, TRANLOG, BEGIN now -- see OGG Reference

EXTRACT added.

Step 3: On the source, use the ADD RMTTRAIL command to specify a remote trail to be created on the target system. Use the EXTRACT argument to link this trail to the Extract group.

GGSCI (HOME-03-DB12) 21> ADD RMTTRAIL Y:\dirdat\rt, EXTRACT extrpt

RMTTRAIL added.

Step 4: On the source, use the EDIT PARAMS command to create a parameter file for the Extract

group. Include the following parameters plus any others that apply to your database environment.

GGSCI (HOME-03-DB12) 22> edit params extrpt

-- Identify the Extract group:

EXTRACT extrpt

-- Specify database login information as needed for the database (source):

USERID ggs_owner@homedev, PASSWORD ggs_owner;

-- Specify the name or IP address of the target system:

RMTHOST HOME-03-DB12, MGRPORT 7809;

-- Specify the remote trail on the target system:

RMTTRAIL Y:\dirdat\rt;

--Specify transaction log options

TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY W:\DEST_1; -- see OGG Reference

TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT HOMEDEV_%S_%R.%T.ARC;

TRANLOGOPTIONS PATHMAP D:\ORACLE\PRODUCT\10.2.0\ORADATA\HOMEDEV\HOMEDEV U:\HOMEDEV\HOMEDEV

-- Specify tables to be captured:

TABLE scott.emp; -- you can put the scott.* if you want to include all the tables.

Note: I used the above tranlogoptions , because my source database (HOMEDEV) was on 32bit windows system and OGG processes were running on another system Win64bit. I made the logs location shared for me and then used the "Map Network Drive" option in windows (64bit) to expose the location to OGG process.

Target system

Step 5: configure the Manager process on the target, configure the Manager process according to the tutotial

In the Manager parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the local trail.

Step 6: On the target, create a Replicat checkpoint table. All Replicat groups can use the same checkpoint table.

GGSCI (HOME-03-DB12) 10> dblogin userid ggs_owner@asmdb, password ggs_owner
Successfully logged into database.
GGSCI (HOME-03-DB12) 11> ADD CHECKPOINTTABLE ggs_owner.CHKPTAB
Successfully created checkpoint table GGS_OWNER.CHKPTAB.

Step 7: On the target, use the ADD REPLICAT command to create a Replicat group. Use the EXTTRAIL argument to link the Replicat group to the remote trail.

GGSCI (HOME-03-DB12) 12> ADD REPLICAT reprpt, EXTTRAIL Y:\dirdat\rt, begin now

REPLICAT added.

Step 8: On the target, use the EDIT PARAMS command to create a parameter file for the Replicat group.

Include the following parameters plus any others that apply to your database environment.

GGSCI (HOME-03-DB12) 14> edit params reprpt

-- Identify the Replicat group:

REPLICAT reprpt
-- State whether or not source and target definitions are identical:
ASSUMETARGETDEFS   -- See GG Reference
-- Specify database login information as needed for the database:
USERID ggs_owner@asmdb, PASSWORD ggs_owner
-- Specify error handling rules:
REPERROR (, )  -- will be discussed in some other post
-- Specify tables for delivery:
MAP scott.EMP, TARGET scott.EMP;

Test your replication , make modification to your source (EMP on source db) and verify on target. 

Related Posts:

Oracle GoldenGate Tutorial Part 1 - Concepts and Architecture

Oracle GoldenGate Tutorial Part 2 - Installation (Windows 2003)

Oracle GoldenGate Tutorial Part 3 - Manager process

Oracle GoldenGate Tutorial Part 4 - Working with OGG

Live Reporting with Data Pump -GG

OGG Reference - Commands and Options

SQL Server to SQL Server Replication using GG

Oracle GoldenGate Tutorial Part 4 - Working with OGG

As we know have basic configurations and information about OGG, we can start with first complete replication example. We will use the initial data load for this example, later on we will see other examples for online synchronization and DDL.

Assumptions

•  Source DB (eg;  HOMEDEV)
•  Target DB (eg;   ASMDB, I've ASM Oracle database as target but you can have without ASM)
•  USER SCOTT is existing on source and target

Step 1:   On Source, start the ggsci session
D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Windows x64 (optimized), Oracle 10 on Jul 28 2010 14:52:12
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
GGSCI (DB2) 1> start mgr
Note: For me the manager process is  running on 7808 for Source system.
Step 2: Create extract process "extload1" on the source system. This will be one time data extract task so the source of the data is not the transaction log files of the database but the data itself. Thus we will use keyword SOURCEISTABLE with the add extract command.
GGSCI (DB2) 4> ADD EXTRACT extload1,SOURCEISTABLE
EXTRACT added.
Step 3: Create the parameter file for the extract group extload1
GGSCI (DB2) 5> edit params extload1 (notepad will be opened set the parameters)
EXTRACT extload1  -- the name of extract created earlier
USERID ggs_owner@homedev,PASSWORD ggs_owner  -- OGG owner doing the work for extract
RMTHOST DB2,MGRPORT 7809  --remote host for the replicat process with manager --process with port 7809
RMTTASK replicat,GROUP repload1  -- remote task type and the group responsible to perform that task
TABLE SCOTT.EMP;  -- Table to be extracted,Assume that EMP table on target (ASMDB) is empty for --this example

Step 4: On Target , create the initial data load task "repload1" for replication. As this will be one time task, we will use the keyword SPECIALRUN
GGSCI (DB2) 4> ADD REPLICAT repload1, SPECIALRUN
REPLICAT added.

GGSCI (DB2) 6> start mgr
Manager started.

Step 5: Create the parameter file for the Replicat group, "repload1"
GGSCI (DB2) 5> edit params repload1 (notepad will be opened, set parameters)

REPLICAT repload1 -- process/group name for replicat
USERID ggs_owner@asmdb, PASSWORD ggs_owner -- OGG db user in target databse
ASSUMETARGETDEFS  -- used when the source and target tables specified with a MAP statement have --identical column structure,
MAP scott.emp, TARGET scott.emp;  -- to establish a relationship between one or more source and target --objects.

Step 6: Start the initial load data extract task on the source system. Since this is a one time task, we will initially see that the extract process is running and after the data load is complete it will be stopped.
We do not have to manually start the Replicat process on the target as that is done when the Extract task is started on the source system.
GGSCI (DB2) 6> start mgr
Manager started.

GGSCI (DB2) 13> start extract extload1

Check on the source SCOTT.EMP and you will see the rows there.

NOTE: As now you are familiar with golden gate, creating parameter files for the processes etc, I'll be posting my further posts without the title Oracle GoldenGate Tutorial.

Related Posts:

Oracle GoldenGate Tutorial Part 1 - Concepts and Architecture

Oracle GoldenGate Tutorial Part 2 - Installation (Windows 2003)

Oracle GoldenGate Tutorial Part 3 - Manager process

Live Reporting with OGG

Live Reporting with Data Pump -GG

OGG Reference - Commands and Options

SQL Server to SQL Server Replication using GG

Oracle GoldenGate Tutorial Part 1 - Concepts and Architecture

What is Oracle Golden Gate
Oracle GoldenGate is a comprehensive software package for enabling the replication of data in heterogeneous data environments.
The product set enables high availability solutions, real-time data integration, transactional change data capture, data replication,
transformations, and verification between operational and analytical enterprise systems.
It enables the exchange and manipulation of data at the transaction level as well as changes to DDL (data definition language2) across a variety of topologies.
With this flexibility, and the filtering, transformation, and custom processing features of
Oracle GoldenGate, you can support numerous business requirements:
● Business continuance and high availability.
● Initial load and database migration.
● Data integration.
● Decision support and data warehousing.

Oracle GoldenGate architecture

Oracle GoldenGate is composed of the following components:
● Extract Process
● Data pump Process
● Replicat Process
● Trails or extract files
● Checkpoints
● Manager Process
● Collector Process

Extract
The Extract process runs on the source system and is the extraction (capture) mechanism
of Oracle GoldenGate. You can configure Extract in one of the following ways:
● Initial loads: For initial data loads, Extract extracts a current set of data directly from
their source objects.
● Change synchronization: To keep source data synchronized with another set of data,
Extract captures changes made to data (typically transactional inserts, updates, and
deletes) after the initial synchronization has taken place. DDL changes and sequences
are also extracted, if supported for the type of database that is being used.
Data pumps
This is the optional GG process runs on the source system. Without data pump process, extract must send data to a remote trail on the target. If you use data pump then primary extract process writes to a trail on the source system. The data pump reads this trail and sends the data over the network to a remote trail on the target.
Replicat
The Replicat process runs on the target system. Replicat reads extracted data changes and
DDL changes (if supported) that are specified in the Replicat configuration, and then it replicates them to the target database.
Trails
To support the continuous extraction and replication of database changes, Oracle GoldenGate stores the captured changes temporarily on disk in a series of files called a trail.
Trail files are created as needed during processing, and they are aged automatically to allow processing to continue without interruption for file maintenance. All file names in a trail begin with the same two characters, which you assign when you create the trail.
Checkpoints
Checkpoints store the current read and write positions of a process to disk for recovery purposes. These checkpoints ensure that data changes that are marked for synchronization actually are extracted by Extract and replicated by Replicat, and they prevent redundant processing.
Manager
Manager is the control process of Oracle GoldenGate. Manager must be running on each system in the Oracle GoldenGate configuration before Extract or Replicat can be started. It performs managerial tasks like monitoring , restarting the golden gate processes , maintaining trail files etc.
Collector
Collector is a process that runs in the background on the target system. Collector receives extracted database changes that are sent across the TCP/IP network, and it writes them to a trail or extract file. Typically, Manager starts Collector automatically when a network connection is required. When Manager starts Collector, the process is known as a dynamic Collector, and Oracle GoldenGate users generally do not interact with it.

Other Concepts
Processing Group
To differentiate among multiple Extract or Replicat processes on a system, you define processing groups.
A processing group consists of a process (either Extract or Replicat), its parameter file, its checkpoint file, and any other files associated with the process. For Replicat, a group also includes a checkpoint table, if one is being used.
Commit Sequence Number (CSN)
A CSN is an identifier that Oracle GoldenGate constructs to identify a transaction for the purpose of maintaining transactional consistency and data integrity. It can be required to position Extract in the transaction log, to reposition Replicat in the trail, or for other purposes.
Each kind of database management system generates some kind of unique serial number of its own at the completion of each transaction, which uniquely identifies that transaction.  Extract writes a normalized form of the CSN to external storage such as the trail files and the checkpoint file.

Related Posts:

Oracle GoldenGate Tutorial Part 2 - Installation (Windows 2003)

Oracle GoldenGate Tutorial Part 3 - Manager process

Oracle GoldenGate Tutorial Part 4 - Working with OGG

Live Reporting with OGG

Live Reporting with Data Pump -GG

OGG Reference - Commands and Options

SQL Server to SQL Server Replication using GG

Oracle GoldenGate Tutorial Part 2 - Installation (Windows 2003)

1- Download the GG software from the following location
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
I used the following for my platform (Windows 2003 Server)
Oracle GoldenGate v11.1.1.0.0 for Oracle 11g 64 bit on Windows XP, 2003, 2008(13 MB)

2- Download Oracle Client ( if you don't have already installed), test your client with some existing Oracle database. You don't need to install the client if you are using the same machine for Oracle Golden Gate where Oracle database is running in my case, my Oracle Databases 10g (source + target) were running on Windows 32bit and I had to install the OGG on Windows 64bit so I installed the Oracle Client on that machine with necessary modification in tnsnames.ora for Source and Target databases.
3- As OGG must be running on source and target do the following to get it installed on both systems.
i) Extract the Oracle GoldenGate zip file by winzip to a drive eg; E:
ii) Run the command shell and change directories to the new Oracle GoldenGate directory.
     C:\Documents and Settings\inam>e:
    
iii) From the Oracle GoldenGate directory, run the GGSCI program.
D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Windows x64 (optimized), Oracle 10 on Jul 28 2010 14:52:12
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
GGSCI (DB2) 1>
iv) In GGSCI, issue the following command to create the Oracle GoldenGate working directories.
GGSCI (DB2) 1> create subdirs
Creating subdirectories under current directory E:\OGG\GoldenGate
Parameter files                D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirprm: created
Report files                   D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirrpt: created
Checkpoint files             D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirchk: created
Process status files          D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirpcs: created
SQL script files            D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirsql: created
Database definitions files    D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirdef: created
Extract data files             D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirdat: created
Temporary files                D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirtmp: created
Veridata files                D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver: created
Veridata Lock files            D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\lock: created
Veridata Out-Of-Sync files     D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\oos: created
Veridata Out-Of-Sync XML files D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\oosxml: created
Veridata Parameter files      D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\params: created
Veridata Report files          D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\report: created
Veridata Status files          D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\status: created
Veridata Trace files           D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\trace: created
Stdout files                   D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirout: created

v) Issue the following command to exit GGSCI.
GGSCI (DB2) 2> Exit

Preparing Database for Golden Gate
We then need to create a database user which will be used by the GoldenGate Manager, Extract and Replicat processes. We create the user GGS_OWNER and grant it the required privileges.
SQL> create tablespace ggs_data
2 datafile ‘D:\oracle\product\10.2.0\oradata\HOMEDEV\HOMEDEV\GGS_DATA.DBF’ size 200m;
SQL> create user ggs_owner identified by ggs_owner
2 default tablespace ggs_data
3 temporary tablespace temp;
User created.
SQL> grant connect,resource to ggs_owner;
Grant succeeded.
SQL> grant select any dictionary, select any table to ggs_owner;
Grant succeeded.
SQL> grant create table to ggs_owner;
Grant succeeded.
SQL> grant flashback any table to ggs_owner;
Grant succeeded.
SQL> grant execute on dbms_flashback to ggs_owner;
Grant succeeded.
SQL> grant execute on utl_file to ggs_owner;
Grant succeeded.

 We can then confirm that the GoldenGate user we have just created is able to connect to the Oracle database

GGSCI (DB2) 4> DBLOGIN  USERID ggs_owner@homedev, PASSWORD ggs_owner
Successfully logged into database.

Enable Supplemental Logging
We need to enable supplemental logging at the database level otherwise we will get this error when we try to start the Extract process -

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

SELECT SUPPLEMENTAL_LOG_DATA_MIN,supplemental_log_data_pk, supplemental_log_data_ui FROM V$DATABASE;

Related Posts:

Oracle GoldenGate Tutorial Part 1 - Concepts and Architecture

Oracle GoldenGate Tutorial Part 3 - Manager process

Oracle GoldenGate Tutorial Part 4 - Working with OGG

Live Reporting with OGG

Live Reporting with Data Pump -GG

OGG Reference - Commands and Options

SQL Server to SQL Server Replication using GG

Oracle GoldenGate Tutorial Part 3 - Manager process

1. From the Oracle GoldenGate directory, run the ggsci program to open the Oracle GoldenGate Software Command Interface, commonly known as GGSCI.
2. In GGSCI, issue the following command to edit the Manager parameter file.
GGSCI (DB2) 1> edit params mgr  (notepad will be opened)
3. Add the following parameter to specify the Manager port number.
PORT 7808 --defines the port number on which Manager runs on the local system.
USERID ggs_owner@homedev,PASSWORD ggs_owner -- db user used by gg processes
PURGEOLDEXTRACTS D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirdat\ex,USECHECKPOINTS  -- allows you to manage trail files in a centralized fashion and take into account multiple processes.

Start/stop Manager from GGSCI

GGSCI (DB2) 2> start mgr
Manager started.

GGSCI (DB2) 3> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.

GGSCI (DB2) 4> info mgr

Manager is DOWN!

Related Posts:

Oracle GoldenGate Tutorial Part 1 - Concepts and Architecture

Oracle GoldenGate Tutorial Part 2 - Installation (Windows 2003)

Oracle GoldenGate Tutorial Part 4 - Working with OGG

Live Reporting with OGG

Live Reporting with Data Pump -GG

OGG Reference - Commands and Options

SQL Server to SQL Server Replication using GG

OGG Reference - Commands and Options

TRANLOG
Use TRANLOG as the data source option, It specifies the transaction log as the data source.Use this option for all databases except Teradata. TRANLOG requires the BEGIN option.
GEGIN
Use the BEGIN parameter to start processing with the first record in the database
transaction log or Oracle GoldenGate trail that has a timestamp greater than, or equal to,
the time specified with BEGIN. All subsequent records, including records where the
timestamp is less than the specified time, are processed.
ASSUMETARGETDEFS
Use the ASSUMETARGETDEFS parameter when the source and target tables specified with a
MAP statement have identical column structure, such as when synchronizing a hot site. It
directs Oracle GoldenGate not to look up source structures from a source-definitions file.
 SOURCEDEFS
Use the SOURCEDEFS parameter to specify the name of a file that contains definitions of
source tables or files. Source definitions are required when using Oracle GoldenGate in a
heterogeneous synchronization environment, where source and target table structures are
different. Use SOURCEDEFS for one or more of the following processes, depending on your
Oracle GoldenGate configuration:
● A Replicat process on the target system
● A data pump on a source or intermediary system.
 TRANLOGOPTIONS
Use the TRANLOGOPTIONS parameter to control aspects of the way that Extract interacts with
the transaction logs. You can use multiple TRANLOGOPTIONS statements in the same
parameter file, or you can specify multiple options within the same TRANLOGOPTIONS
statement, if permissible for those options.
Use a given TRANLOGOPTIONS option only for the database or databases for which it is
intended.
Option ALTARCHIVEDLOGFORMAT:  (Oracle) Specifies a string that overrides the archive log
format of the source database. accepts the same specifier as Oracle's parameter LOG_ARCHIVE_FORMAT. Extract uses the supplied format specifier to derive the log file name.
Option ALTARCHIVELOGDEST:  (Oracle) Points Extract to the archived or backup Oracle
transaction logs when they reside somewhere other than the default location. Extract first checks the specified
location and then checks the default location.
Option PATHMAP : (Oracle) Use to specify the location of the redo and/or
archived logs when they are stored on a system other than the one which is hosting the database. Specify the NFS mount point followed by the path to the Oracle log structure(s). More than one PATHMAP statement can be used.

Related Posts:

Oracle GoldenGate Tutorial Part 1 - Concepts and Architecture

Oracle GoldenGate Tutorial Part 2 - Installation (Windows 2003)

Oracle GoldenGate Tutorial Part 3 - Manager process

Oracle GoldenGate Tutorial Part 4 - Working with OGG

Live Reporting with OGG

Live Reporting with Data Pump -GG

SQL Server to SQL Server Replication using GG

Administring ASM Instance remotely (10gR2)

By default the ASM instance is blocked and remote connections and administration cannot be performed.  The blocked status is being set by the instance, indicating that the database is not able to accept connections. This can be caused by the instances not being mounted or mounted in a restricted state.
Since an ASM instance never mounts a database, it will always be shown in 'BLOCKED' status.
The administration of an ASM instance is intended to be done on the server itself and not remotely.
If you intend to manage the ASM instance remotely , following solution can be applied.

Solution:
Connecting to a 'BLOCKED' instance via a listener requires creating an entry in the tnsnames.ora file on the client side with 'UR=A' under CONNECT_DATA.
Also, creating a password file for the ASM instance will be required to connect as SYS.

Step 1: Check the ASM instance by connecting as sysdba on the server itself
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\inam>set ORACLE_SID=+ASM

C:\Documents and Settings\inam>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 1 10:51:32 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
Step 2: Create entry for ASM instance in tnsnames.ora on client machine and connect remotely
+ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.domain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )

Try to access the asm instance with the new service
C:\Documents and Settings\inam>sqlplus sys/asmdb@+asm as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 1 10:17:49 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Enter user-name:


Step 3: Check status with lsnrctl
C:\Documents and Settings\inam>lsnrctl status listener

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 01-DEC-2010 10:18:49

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.domain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date                30-NOV-2010 10:10:40
Uptime                    1 days 0 hr. 8 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File         D:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv2.domain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv2.domain)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv2.domain)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+asm", status BLOCKED, has 1 handler(s) for this service...
Service "+asm" has 1 instance(s).
  Instance "+asm", status BLOCKED, has 1 handler(s) for this service...
Service "ASMDBXDB" has 1 instance(s).
  Instance "asmdb", status READY, has 1 handler(s) for this service...



Step 4: Create password file for ASM instance. For my example i did the following to create the password file.
- found password file in folder  "D:\oracle\product\10.2.0\db_1\database" for ASMDB database which is using ASM instance for storage.
-  copy . ie; 
D:\oracle\product\10.2.0\db_1\database>copy PWDASMDB.ORA "PWDASM.ORA"
        1 file(s) copied.
Now those users with SYSDBA privileges in the password file can be used as the ASMUSER.
WARNING: This will change the SYS password in the ASM instance to the value used in the database instance.
Step 5:  Following line must be in listener.ora on Server
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.domain)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = +ASM)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
    )
  )       

Step 6: Update the tnsnames.ora on client
+ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.domain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
      (UR=A)
    )
  )


Step 7: Try now to connect to +ASM instance remotely using the sqlplus client.
C:\Documents and Settings\inam>sqlplus sys/asmdb@+asm as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 1 10:39:11 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> 

Changing HTTP Server Port (Middle Tier)

Replace "Listen 7779" with "Listen 80", this time, instead of replacing it, have both sit together one after the other as follows  in %ORACLE_HOME%\Apache\Apache\conf\httpd.conf

Listen 7779
Listen 80

FRM-41838 Unable to open temporary record buffer file

One of the our developer encountered the following error
FRM-41838 Unable to open temporary record buffer file  C:\DOCUMEN~1\LOCALS~1\Temp\1\s7e8.2
Cause
Oracle Forms tries to create a temporary file if a query is issued and the number of records returned exceeds the block property 'Records Buffered' value. When using OracleAS Forms Services, the temporary file will be written by default to the directory specified for the TMP variable in:
$ORACLE_HOME/opmn/conf/opmn.xml.
If this directory does not exist, has insufficient space or has restricted read / write privileges the FRM-41838 error can occur.
Solution
Solution A:
1. Edit the Forms Listener Servlet env file. This will be the file referenced by the formsweb.cfg applet parameter envFile. By default, envFile = default.env, default.env is located in $ORACLE_HOME/forms90/server or in $ORACLE_HOME/forms/server
2. Add the line e.g.
TMP=/tmp
(assuming that the directory specified e.g. /tmp has sufficient space and read/write privileges)
3. Stop and restart the OC4J_BI_Forms component for the change to take effect
Solution B:
Change the value for the TMP variable setting in opmn.xml by following all the steps below:

1. Edit $ORACLE_HOME/opmn/conf/opmn.xml where $ORACLE_HOME corresponds to the Oracle Application Server middle tier home (BI & Forms)

2. Look for the line e.g. on Unix



3. Check that the directory specified exists, has sufficient space and read/write privileges. If necessary, change the TMP value e.g.



4. If the directory has been changed in opmn.xml, stop and restart all the middle tier components for the change to take effect.

ORA -3136 WARNING inbound connection timed out

The "WARNING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete  it's authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.
You may also witness ORA-12170 without timeout error on the database server sqlnet.log file.
This entry would also have the client address which failed to get authenticated. Some applications or JDBC thin driver applications may not have these details.
From 10.2.0.1 onwards the default value of parameter SQLNET.INBOUND_CONNECT_TIMEOUT is 60 seconds, hence if the client is not able authenticate within 60 secs , the warning would appear in the alert log and the client connection will be terminated.
There can be three main reasons for this error -

1. Server gets a connection request from a malicious client which is not supposed to connect to the database , in which case the error thrown is the correct behavior. You can get the client address for which the error was thrown via sqlnet log file.
2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.
3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified. 

To understand what is causing this issue, following checks can be done
The default value of 60 seconds is good enough in most conditions for the database server to authenticate a client connection. If it is taking longer, then its worth checking all the below points before going for the workaround:

1. Check whether local connection on the database server is successful & quick.
2. If local connections are quick ,then check for underlying network delay with the help of your network administrator.
3. Check whether your Database performance has degraded in anyway.
4. Check alert log for any critical errors for eg, ORA-600 or ORA-7445 and get them  resolved first.
These critical errors might have triggered the slowness of the database server.

As a workaround to avoid only this warning messages, you can set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT
and
 INBOUND_CONNECT_TIMEOUT_
to the value more than 60.

For e.g 120. So that the client will have more time to provide the authentication information to the database. You may have to further tune these parameter values according to your setup.

To set these parameter -

1. In server side sqlnet.ora file add

SQLNET.INBOUND_CONNECT_TIMEOUT

For e.g

SQLNET.INBOUND_CONNECT_TIMEOUT = 120 

2. In listener.ora file -

INBOUND_CONNECT_TIMEOUT_ = 110

For e.g if the listener name is LISTENER then -  

INBOUND_CONNECT_TIMEOUT_LISTENER = 110 

How to check whether inbound timeout is active for the listener and database server:
For example,  INBOUND_CONNECT_TIMEOUT_ =110

You can check whether the parameter is active or not by simply doing telnet to the listener port.
$ telnet
for eg.

$ telnet 123.23.23.23 1521

The telnet session should disconnect after 110 seconds which indicates that the inbound connection timeout for the listener is active.

Alternatively, check at the LSNRCTL prompt using:

LSNRCTL>set current_listener
LSNRCTL>show inbound_connect_timeout

To check whether database server SQLNET.INBOUND_CONNECT_TIMEOUT is active:
Eg.

SQLNET.INBOUND_CONNECT_TIMEOUT=120

a. For Dedicated server setup, enable the support level sqlnet server tracing will show the timeout value as below:

niotns: Enabling CTO, value=120000 (milliseconds) <== 120 seconds
niotns: Not enabling dead connection detection.
niotns: listener bequeathed shadow coming to life...

b. For shared Server setup,
$ telnet
Example.

$ telnet 123.23.23.23  51658

The telnet session should disconnect after 120 seconds which indicates that the sqlnet.inbound_connect_timeout is active.

Ref: 465043.1

An Error Occurred While Starting OPMN Integrated Standalone Reports Server

While starting an OPMN integrated Standalone Reports Server via OEM, the following error is encountered :
An error occurred while starting "Reports Server: ".
 : ReportsServer - time out while waiting for a managed process to start
For more information, look at the logs using the related link below.
Related Link Error Logs

Cause
The value specified in the "REPORTS_TMP" environment variable was invalid because there was no directory named "C:\DOCUME~1\dev\LOCALS~1\Temp\1".

Solution
To implement the solution, please execute the following steps :

1. Go to Windows Registry.
2. Navigate to HKEY_LOCAL_MACHINE -> SOFTWARE -> Oracle.
3. Select the HOME under Oracle where the Oracle 10g Application Server is installed.
4. Change the value of the "REPORTS_TMP" environment variable from "C:\DOCUME~1\dev\LOCALS~1\Temp\1" to an existing directory. For example  "c:\tmp".
5. Now start the Reports Server and it will start properly.

FRM-92101 Error When Running Forms After 70-75 Concurrent Connections and No New Connection Possible

This problem is specific to MS Windows Operating Systems - normally Windows 2000 or 2003.  Although the occurrence of this issue is not common, it appears on both 32 and 64 bit windows.  You may receive the following error indications:

Forms 6i, 9.0.4.X, 10.1.2.X - FRM-92050 sometimes together with FRM-92101 or FRM-92102
Forms 9.0.4.X, 10.1.2.X - application.log shows FRM-93000
Forms 6i (Forms Listener Servlet) - jserv.log shows FRM-93000
Forms 6i (Forms Server) - error log shows ERROR 1444 - FORMS SERVICE STOPS UNEXPECTEDLY 1444

This happens with a small number of connections - the range may be 50 - 80. The specific number depends on operating system factors such as number of CPUs, processor speed, memory, etc.   The problem may occur even with less than 50.  

Cause

Windows has a memory resource called the Desktop Heap. A particular section of this  resource is running low and causes the problem.

Solution

1. Change the logon-property from the responsible service to "[x] Allow service to interact with desktop" (using all the defaults of the registry!) and restart the service.

This forces the Apache Service (Forms 6i) or the Oracle Process Manager Service (OPMN) (Forms 9i and above) to use the IO Desktop heap which is larger than the Non-IO Desktop heap by default. The term "Desktop", here, is not to be confused with the normal Windows desktop, which holds your icons and your background, etc. In this context, it is Microsoft terminology for an area of memory.

Once verified that this increases the number of possible frmweb processes, then return this setting back to its default value - unchecked -  and modify the Non-IO Desktop Heap size in the Registry as it is used by the Middle Tier OPMN Service.
2. Modify the Non-IO Desktop Heap size variable in the registry. This should not be done unless:
- A backup has been made of the registry. Any error in modification of the registry can render the system unusable.

- Solution 1 has been tested and has increased the number of processes that will run successfully.

The following information applies to Windows NT, Windows 2000, Windows 2003.
Enter the Registry and go to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\SubSystems.

Double click on the Windows node value in the right frame.  Increase the  appropriate SharedSection parameter which will be the third one.  For example, the SharedSection specifies the system and desktop heaps using the following format: 

SharedSection=xxxx,yyyy,zzzz The default values are normally 1024,3072,512

All the values are in kilobytes (KB).

xxxx = System-wide Heapsize. There is no need to modify this value and it is ignored for the rest of this discussion.

yyyy = IO Desktop Heapsize. This is the heap for memory objects in the IO Desktop and there is normally no need to modify this value. Programs that are run from a command line (.bat files) or an icon use this memory resource.

zzzz = Non-IO Desktop Heapsize. This is the heap for memory objects in the Non-IO Desktop and is used by programs that are spawned from a service. Therefore, this is the value that should be increased if the OPMN process is being started as a service which is the default for a windows installation of the Application Server. This number can be set as high as 30 MB (30720 KB). However, it is seldom necessary to set it this high.

If you change these values, you must reboot the system.
The values are recommended to be a multiples of 512, but this isn't necessary. There is a hard limit total of 48 Mb for the the total heap size for Windows.    

 

Data Encryption

The DBMS_OBFUSCATION_TOOLKIT package provides a simple API for data encryption. Oracle8i only provides DES encryption whilst Oracle9i also includes DES3 encryption. In this article I'll create a simple package containing functions to DES encrypt and decrypt test strings:

1. Create the Package
2. Create the Package Body
3. Test It
4. Encrypt Table Data

Create the Package
First we create the package header containing the two conversion functions.
CREATE OR REPLACE PACKAGE toolkit AS

  FUNCTION encrypt (p_text  IN  VARCHAR2) RETURN RAW;
  
  FUNCTION decrypt (p_raw  IN  RAW) RETURN VARCHAR2;
  
END toolkit;
/
Create the Package Body
All VARCHAR2 inputs are padded to multiples of 8 charaters, with the encryption key also being a multiple of 8 charaters. The encryption key and padding characters can be altered to suit.
CREATE OR REPLACE PACKAGE BODY toolkit AS

  g_key     RAW(32767)  := UTL_RAW.cast_to_raw('12345678');
  g_pad_chr VARCHAR2(1) := '~';

  PROCEDURE padstring (p_text  IN OUT  VARCHAR2);


  -- --------------------------------------------------
  FUNCTION encrypt (p_text  IN  VARCHAR2) RETURN RAW IS
  -- --------------------------------------------------
    l_text       VARCHAR2(32767) := p_text;
    l_encrypted  RAW(32767);
  BEGIN
    padstring(l_text);
    DBMS_OBFUSCATION_TOOLKIT.desencrypt(input          => UTL_RAW.cast_to_raw(l_text),
                                        key            => g_key,
                                        encrypted_data => l_encrypted);
    RETURN l_encrypted;
  END;
  -- --------------------------------------------------



  -- --------------------------------------------------
  FUNCTION decrypt (p_raw  IN  RAW) RETURN VARCHAR2 IS
  -- --------------------------------------------------
    l_decrypted  VARCHAR2(32767);
  BEGIN
    DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw,
                                        key   => g_key,
                                        decrypted_data => l_decrypted);
                                        
    RETURN RTrim(UTL_RAW.cast_to_varchar2(l_decrypted), g_pad_chr);
  END;
  -- --------------------------------------------------


  -- --------------------------------------------------
  PROCEDURE padstring (p_text  IN OUT  VARCHAR2) IS
  -- --------------------------------------------------
    l_units  NUMBER;
  BEGIN
    IF LENGTH(p_text) MOD 8 > 0 THEN
      l_units := TRUNC(LENGTH(p_text)/8) + 1;
      p_text  := RPAD(p_text, l_units * 8, g_pad_chr);
    END IF;
  END;
  -- --------------------------------------------------

END toolkit;
/
Test It
We can test the basic functionality using the following code.
DECLARE
  l_value VARCHAR2(16) := 'ORACLE-BASE';
  l_raw   RAW(16);
BEGIN
  DBMS_OUTPUT.put_line('l_value: ' || l_value);
  l_raw := toolkit.encrypt(l_value);
  DBMS_OUTPUT.put_line('l_raw: ' || l_raw);
  DBMS_OUTPUT.put_line('Original Value : ' || toolkit.decrypt(l_raw));
END;
/
Remember that the length of the output from the encryption routine will be rounded up to the next multiple of 8 characters. If the results are to be stored as RAW datatypes in the database you must make sure enough room is allocated. SQL*Plus displays the contents of RAW variable in HEX so it appears to be twice as long as it actually is.
Encrypt Table Data
The following code provides a simple example of how data in a table might be encrypted using a trigger. First we must create a test table with an appropriate trigger.
CREATE TABLE encrypted_data (
  username  VARCHAR2(20),
  data      RAW(16)
);

CREATE OR REPLACE TRIGGER encrypted_data_biur_trg
BEFORE INSERT OR UPDATE ON encrypted_data
FOR EACH ROW
DECLARE
BEGIN
  :new.data := toolkit.encrypt(UTL_RAW.cast_to_varchar2(:new.data));
END;
/
Next we test the trigger using some simple insert, update and query statements.
SQL> INSERT INTO encrypted_data (username, data)
  2  VALUES ('tim_hall', UTL_RAW.cast_to_raw('My Secret Data'));

1 row created.

SQL> SELECT * FROM encrypted_data;

USERNAME             DATA
-------------------- ----------------------------------------
tim_hall             FA57C55510D258C73DE93059E3DC49EC

1 row selected.

SQL> COLUMN data FORMAT A40
SQL> SELECT username, toolkit.decrypt(data) AS data FROM encrypted_data;

USERNAME             DATA
-------------------- ----------------------------------------
tim_hall             My Secret Data

1 row selected.

SQL> UPDATE encrypted_data
  2  SET    data     = UTL_RAW.cast_to_raw('My NEW Secret')
  3  WHERE  username = 'tim_hall';

1 row updated.

SQL> COLUMN data FORMAT A40
SQL> SELECT username, toolkit.decrypt(data) AS data FROM encrypted_data;

USERNAME             DATA
-------------------- ----------------------------------------
tim_hall             My NEW Secret

1 row selected.

SQL>
With the exception of the calls to the UTL_RAW package, this method hides most of the work from the developer.For more information see:

1. DBMS_OBFUSCATION_TOOLKIT

Remote Desktop Disconnection Problem

If following error is encountered , perform the solution given.

Delete this registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSLicensing from your PC

View redo logs by logminer

Step 1: Check supplemental log is enabled for database,  if not enable it
connect / as sysdba;
 SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; -- it should be yes for logmining
 alter database add supplemental log data; --so that db can store additional data for logmining
Step 2: Run the following block to add logs and start logminer .
BEGIN
     DBMS_LOGMNR.ADD_LOGFILE ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO03.LOG', DBMS_LOGMNR.NEW);     
    DBMS_LOGMNR.ADD_LOGFILE ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO02.LOG', DBMS_LOGMNR.ADDFILE);
    DBMS_LOGMNR.ADD_LOGFILE ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG', DBMS_LOGMNR.ADDFILE);
    DBMS_LOGMNR.START_LOGMNR ( options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
END;
Step 3: Mine the logs
Use the database control file to find and add redo log files that satisfy your specified time or SCN range to the LogMiner redo log file list.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

BEGIN
     DBMS_LOGMNR.START_LOGMNR(STARTTIME => '01-Mar-2010 15:30:00',ENDTIME => '02-Mar-2010 15:45:00',
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
END;

Step 4: Choose any object to view the logs about it
select sql_redo from v$logmnr_contents where table_name = 'CUST_PAYMENT_INFO' and operation='INSERT';

SELECT SQL_REDO FROM V$LOGMNR_CONTENTS
   WHERE
   SEG_NAME = 'CUST_PAYMENT_INFO' AND
   SEG_OWNER = 'OE' AND
   OPERATION = 'UPDATE' AND
   DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'OE.ACTIVE_CARD') !=   DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'OE.ACTIVE_CARD');