Nov 20

Install Oracle Fusion Middleware for GoldenGate Monitor 12c – PART1

Below are the steps to install the Oracle Fusion Middleware Infrastructure 12c required to install Oracle GoldenGate Monitor Pack 12c.

This is PART 1 of a series of articles on how to install the Oracle GoldenGate Monitor 12c.

PART 2: Install Oracle GoldenGate Monitor

PART 3: Create OGG Monitor Repository

PART 4: Configuration of Monitor Server and Client Agent

Before start the install, make sure you have the JDK.

  • Oracle JDK 7 Update 15 or higher

————————————————————————————————————————————-

[gguser@spbnaggmon01 ~]$ /usr/java/jdk1.7.0_71/bin/java -jar fmw_12.1.3.0.0_infrastructure.jar
Launcher log file is /tmp/OraInstall2014-11-20_11-53-03AM/launcher2014-11-20_11-53-03AM.log.
Extracting files……….
Starting Oracle Universal Installer

Checking if CPU speed is above 300 MHz.   Actual 2396.863 MHz   Passed
Checking monitor: must be configured to display at least 256 colors. Actual unknown.   Failed <<<<
Checking swap space: must be greater than 512 MB.   Actual 8388604 MB   Passed
Checking if this platform requires a 64-bit JVM.   Actual 64   Passed (64-bit not required)
Checking temp space: must be greater than 300 MB.   Actual 911 MB   Passed

Some system prerequisite checks failed.
You must fulfill these requirements before continuing with the installation.

Continue? (yes [y] / no [n]) [n]
y
You have confirmed that the product can be installed on this platform.
Continuing with the installation.

>>> Ignoring failure(s) of required prerequisite checks. Continuing…
Preparing to launch the Oracle Universal Installer from /tmp/OraInstall2014-11-20_11-53-03AM
Log: /tmp/OraInstall2014-11-20_11-53-03AM/install2014-11-20_11-53-03AM.log
You can find the log of this install session at:
/tmp/OraInstall2014-11-20_11-53-03AM/install2014-11-20_11-53-03AM.log
Logs successfully copied to /ggmon/oracle/oraInventory/logs.

 

fmw0

fmw1

fmw2

fmw3

fmw4

fmw5

fmw6

fmw8

fmw9

fmw10

 

In PART 2 I will demonstrate how to install the Oracle GoldenGate Monitor 12c on top of the Fusion Middleware Infrastructure.

Nov 19

Oracle Release Major Enhancements to Oracle GoldenGate 12c

Oracle GoldenGate 12c provides best-of-breed, real-time data integration and heterogeneous database replication. New features include:

  • Migration utility for Oracle Streams: A new migration utility, Streams2OGG helps Oracle Streams customers move to Oracle GoldenGate and leverage its latest features, such as integrated capture and delivery processes and advanced conflict management.
  • Support for IBM Informix: Oracle GoldenGate 12c now supports real-time data capture and delivery for the latest Informix database versions on all major platforms. Support has also been extended to Oracle GoldenGate Veridata so IBM Informix customers can integrate and replicate high volumes of real-time data throughout their heterogeneous business environments.
  • Extended support for Microsoft SQL Server and MySQL: The new release adds support for real-time, log-based capture and delivery for Microsoft SQL Server 2012 and 2014 as well as MySQL Community Edition databases.
  • SOCKS5 compliance: Oracle GoldenGate 12c now leverages customers’ SOCKS compliance setting for data transfer, enabling customers to replicate between on-premises and cloud environments without an extra VPN connection open.
  • Support for big data: Oracle GoldenGate Adapter for Java enables integration with Oracle NoSQL, Apache Hadoop, Apache HDFS, Apache HBase, Apache Storm, Apache Flume, Apache Kafka, and others and allows real-time, noninvasive data streaming into big data targets to give customers new insights into business and improve the customer experience.
  • Out-of-sync data repair: In addition to comparing heterogeneous databases and reporting data discrepancies without interrupting business operations, Oracle GoldenGate Veridata 12c now provides data repair and revalidation capabilities for out-of-sync data.
  • Data capture from Oracle Active Data Guard: Oracle Active Data Guard customers who want to remove any replication impact on their production environment can use Oracle GoldenGate 12c on their standby systems to capture data in real time.
  • Simplified application upgrades: The new release simplifies the upgrade process for customers leveraging the Oracle Database Edition-Based Redefinition feature by coordinating the upgrades and bringing the target database to the same edition-based version.
  • Improved management and monitoring: Customers using the new release also gain the ability to start and stop processes, edit parameter files, collect information about operations, and diagnose issues easily with Oracle Enterprise Manager Plug-In 12.1.3, part of Oracle Management Pack for Oracle GoldenGate 12c.

Should you need help implementing your GoldenGate Strategy feel free to contact us (403) 479-9861

Full release from Oracle:  http://www.oracle.com/us/corporate/press/2368822 

 

 

Aug 15

Simple Oracle GoldenGate Monitor Script

 

Oct 31

Script to generate tablespace creation DDL

 

Oct 31

RMAN backup shell script

#!/bin/ksh

export ORACLE_HOME=/u01/app/oracle/product/10.2.0.4
export ORACLE_SID=$1

export VDATE=$(date +”%m-%d-%Y_%H-%M-%S”)
export BACKUP_LOG_DIR=/u99/dba/scripts/backup/log
export BACKUP_LOG_FILE=${BACKUP_LOG_DIR}/${ORACLE_SID}_rman_${VDATE}.log

export DBA=monitor@xxxx.com

$ORACLE_HOME/bin/rman target / log ${BACKUP_LOG_FILE} << EOF
connect catalog rman/rman@RMANPRD
RUN {
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
delete noprompt obsolete;
}
EXIT;
EOF

if [ -f ${BACKUP_LOG_FILE} ]; then
egrep ‘(ERROR|error|Error|RMAN-)’ ${BACKUP_LOG_FILE} > /dev/null
if [ $? = 0 ]; then
RESULT_MSG=”WARNING: Errors occurred during the ${ORACLE_SID} Rman backup full.”
mail -s “$RESULT_MSG” $DBA < ${BACKUP_LOG_FILE}
else
egrep ‘(Recovery Manager complete)’ ${BACKUP_LOG_FILE} > /dev/null
if [ $? = 0 ]; then
RESULT_MSG=”${ORACLE_SID} RMAN Backup Full Completed Successfully.”
mail -s “$RESULT_MSG” $DBA < ${BACKUP_LOG_FILE}
else
RESULT_MSG=”WARNING: ${ORACLE_SID} RMAN Backup full  did not complete.”
echo Backup process was terminated. | mail -s “$RESULT_MSG” $DBA
fi
fi
fi

##mail -s “Oracle ${ORACLE_SID} backup completed” monitor@xxxx.com < ${BACKUP_LOG_FILE}

May 28

How to get dump or list parameters set at session level?

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters.

SQL> alter session set sql_trace=true;
Session altered.

SQL> alter session set optimizer_mode=first_rows;
Session altered.

-- connect to session
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump modified_parameters 1;
Statement processed.
SQL> oradebug tracefile_name;
i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_4908.trc

Contents of the tracefile:

*** 2012-05-28 14:35:25.005
Processing Oradebug command ‘dump modified_parameters 1′
DYNAMICALLY MODIFIED PARAMETERS:
sql_trace = TRUE
optimizer_mode = FIRST_ROWS

*** 2012-05-28 14:35:25.005
Oradebug command ‘dump modified_parameters 1′ console output:

May 28

How to see current utilization of processes/sessions and max utilization?

Using the following SQL one can find the current number of processes and sessions connected and also max utilization so one can check if you need to increase the values of the parameter

 

May 13

Example of using sql profile to use switch to a different execution plan

Below are the 2 SQL statements, the first one uses the index AAA_IDX and then second one does a full table scan and in this case we want to make the first one that uses the index use the same execution plan as the 2nd one.

SQL statement:
select * from aaa e where ename = 'aaa';
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'

By query v$sql we found the sql_id, child_number and plan_hash_value also checked there isn't a sql_profile attached to the SQL.

SQL> select sql_id, child_number, sql_profile, plan_hash_value, sql_text from v$sql where sql_id IN ('63cg18v928540', '0tjtg6yqqbbxk');

SQL_ID CHILD_NUMBER SQL_PROFILE PLAN_HASH_VALUE
————- ———— ————————————————- —————
SQL_TEXT
————————————————-
63cg18v928540 0 2022030255
select * from aaa e where ename = 'aaa'

0tjtg6yqqbbxk 0 864433273
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'

Displaying the execution plan of the 2nd SQL with the outline option
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('0tjtg6yqqbbxk', 0, 'outline'));
PLAN_TABLE_OUTPUT
——————————————————————————–

SQL_ID 0tjtg6yqqbbxk, child number 0
————————————-
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'

Plan hash value: 864433273

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| AAA | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–

Outline Data
————-

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
FULL(@”SEL$1″ “E”@”SEL$1″)
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
—————————————————

1 – filter(“ENAME”='aaa')

From the outline generated used the hint to add to first SQL to change its execution plan i.e. full table scan.
define SQL_ID = '63cg18v928540';

DECLARE
clsql_text CLOB;
BEGIN
SELECT sql_fulltext INTO clsql_text FROM V$sqlarea where sql_id = '&SQL_ID';
dbms_sqltune.import_sql_profile(sql_text => clsql_text,
profile=> sqlprof_attr('FULL(@SEL$1 E@SEL$1)'),
name=>'PROFILE_&SQL_ID',
force_match=>true);
end;
/

Shows now when we execute the SQL it uses the same execution plan as the 2nd one i.e. full table scan
SQL> select * from aaa e where ename = 'aaa';
Execution Plan
———————————————————-
Plan hash value: 864433273

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| AAA | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“ENAME”='aaa')

Note
—–
– SQL profile “PROFILE_63cg18v928540″ used for this statement