Disabling Oracle Streams Propagation when nothing else works

October 14, 2011 Leave a comment

It’s been a while since I  wrote any post .. I need vacation too :)

Here is very short one but useful tip for folks that are using Oracle Streams.

I know everyone is talking about GoldenGate but trust  me Oracle Streams are good solution if  you do not have budget for GoldenGate.Oracle is working very hard to implement best of Streams into GG.

Anyway this tip shows how to disable Oracle Streams propagation when nothing else is working.At least in my case..

I was trying to stop Oracle Streams propagation process but for some reason nothing did not work..  Finally I realized that Oracle Streams propagation process is related to Oracle jobs and by  quickly disabling (  remember there are other jobs that runs ) oracle scheduler I was able to stop Streams propagation process .

First disable scheduler:

SQL> exec dbms_scheduler.set_scheduler_attribute(‘SCHEDULER_DISABLED’,’TRUE’);
PL/SQL procedure successfully completed.

Stop propagation:

SQL> exec dbms_propagation_adm.stop_propagation(‘TEST_PROPAGATION’, true);

PL/SQL procedure successfully completed.

and enabling scheduler again.

SQL>  exec dbms_scheduler.set_scheduler_attribute(‘SCHEDULER_DISABLED’,’FALSE’);

Categories: Troubleshooting

Export / Import data over db link – using dbms_datapump and dbms_file_transfer packages

June 14, 2011 Leave a comment

– Datapump FULL MODE EXPORT

DECLARE
h1                NUMBER;       — data pump job handle
job_state         VARCHAR2(30); — job state
status            ku$_Status; — data pump status
job_not_exist     exception;
pragma            exception_init(job_not_exist, -31626);
BEGIN

h1 := dbms_datapump.open(operation=>’EXPORT’,job_mode=>’FULL’,
remote_link=>”,
job_name=>NULL, version=>’COMPATIBLE’);

dbms_datapump.add_file(
handle=>h1,
filename=>’test.dmp’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(
handle=>h1,
filename=>’test.log’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_log_file);

dbms_datapump.start_job(h1);

job_state := ‘UNDEFINED';
BEGIN
WHILE (job_state != ‘COMPLETED’) AND (job_state != ‘STOPPED’) LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line(‘job finished’);
END;

– Transfer dump file to the destination directory
dbms_file_transfer.put_file(
source_directory_object => ‘”DPUMP_DIR”‘,
source_file_name => ‘test.dmp’,
destination_directory_object => ‘”DPUMP_DIR”‘,
destination_file_name => ‘test.dmp’,
destination_database => ‘test.world’);

COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

PROMPT Connecting as  administrator to site 2
CONNECT username/password
— Datapump FULL MODE IMPORT

DECLARE
h1                NUMBER;       — data pump job handle
job_state         VARCHAR2(30); — job state
status            ku$_Status; — data pump status
job_not_exist     exception;
pragma            exception_init(job_not_exist, -31626);
BEGIN

h1 := dbms_datapump.open(operation=>’IMPORT’,job_mode=>’FULL’,
remote_link=>”,
job_name=>NULL, version=>’COMPATIBLE’);

dbms_datapump.set_parameter(h1, ‘STREAMS_CONFIGURATION’, 0);

dbms_datapump.add_file(
handle=>h1,
filename=>’test.dmp’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(
handle=>h1,
filename=>’test.log’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_log_file);

dbms_datapump.start_job(h1);

job_state := ‘UNDEFINED';
BEGIN
WHILE (job_state != ‘COMPLETED’) AND (job_state != ‘STOPPED’) LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line(‘job finished’);
END;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

Categories: Tools

Oracle GoldenGate and Encrypted Tablespaces (TDE) in 11.2

May 16, 2011 1 comment

To make GG work with encrypted tablespaces  you have to apply database patch:

Patch  10395645 for Oracle 11.2.0.2.

mkstore -wrl ./ -createEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

Supply shared secret for GG ( not wallet password).Last step is to supply wallet password.Copy ewallet.p12 file to other nodes ( if running RAC but not sharing wallet location ).
Do not forget standby databases if you have them.
If you like to see shared secret for GG you can run:

 mkstore -wrl  /u01/app/oracle/wallet -viewEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
Create package  dbms_internal_clkm.Package code get created by running script prvtclkm.plb  located under GG home .Once package is created grant execute privileget to GG user:

grant execute on  on thedbms_internal_clkm to gguser;

This package has only one procedure  called GET_KEY with following parameters:

SQL> desc dbms_internal_clkm
PROCEDURE GET_KEY
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 CLIENT                         VARCHAR2                IN
 MASTER_KEY_ID                  VARCHAR2                IN
 WRAPPED_KEY                    VARCHAR2                IN
 FLAGS                          BINARY_INTEGER          IN
 KEY                            VARCHAR2                OUT
Encrypt the Shared secret key
GGSCI> ENCRYPT PASSWORD “shared key”
Add an entry to the Extract parameter file to decrpt the new shared password

DBOPTIONS  DECRYPTPASSWORD “SHARED KEY”
Close and open wallet to clear caches.If you have wallet enabled with auto login ( file cwallet.sso ) than disable it temporary to close and open wallet.

Categories: HA

Oracle GoldenGate version 11.1.1.1 released

May 10, 2011 2 comments

After long wait Oracle finaly relesed new Oracle GoldenGate version 11.1.1.1 which supports encrypted tablespaces.

To download it click on the link below:

https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=13730870

Also there is new documentation for this release:

http://download.oracle.com/docs/cd/E22355_01/index.htm

About  Replicating TDE-encrypted data

  Oracle GoldenGate supports the Transparent Data Encryption (TDE) at the column and tablespace level.

● Column-level encryption is supported for all versions of 10.2.0.5, 11.1, and 11.2.

● Tablespace-level encryption is supported for all versions of 10.2.0.5 and 11.1.0.2.

 Required database patches

  To support TDE, one of the following Oracle patches must be applied to the database,

depending on the version.

 ● Patch 10628966 for 10.2.0.5.2PSU

● Patch 10628963 for 11.1.0.7.6PSU

● Patch 10628961 for 11.2.0.2

Categories: HA

Script – How to find table inside PL/SQL

April 12, 2011 1 comment

SELECT owner,
       object_type,
       object_name,
       object_id,
       status
  FROM sys.DBA_OBJECTS
 WHERE object_id IN
          (    SELECT object_id
                 FROM public_dependency
           CONNECT BY PRIOR object_id = referenced_object_id
           START WITH referenced_object_id =
                         (SELECT object_id
                            FROM sys.
                                  DBA_OBJECTS
                           WHERE     owner = ‘<owner_name>’
                            AND object_name ='<table>’
                            AND object_type = ‘TABLE’))

Categories: Tools

Oracle Diagnostic Events in 11g

March 8, 2011 1 comment

This was presented at HOTSOS 2011

Oracle 11g has a completely new low-level kernel diagnostics and tracing infrastructure built into it. This presentation will explore new diagnostic events. It covers topics like: how to find Oracle internal documentation about events, sample cases, demos, etc.

You can download it from these links:

PDF file:

Oracle_Diagnostic_Events_in_11g

Presentation:

Oracle Diagnostic Events

Categories: Internals

OEM 11g install and ORA-12650

February 16, 2011 3 comments

Two weeks ago I was trying to  install  OEM grid control version 11 .I did it many times already but this time my process  failed in the middle of install.

After digging numerous number of  install logs I have found message like one bellow:

HealthMonitor Jan 26, 2011 5:39:44 PM OMS Heartbeat Recorder:  error: Could not get id: java.sql.SQLException: Oracle Error ORA-12650
Critical error err=3 detected in module OMS Heartbeat Recorder:

Quick search turn out that    ORA-12650 No common encryption or data integrity algorithm  is error related to  Oracle Advanced Security Option.

Then I realized that have ASO enabled on datatabase which will be used as OEM respository.

My sqlnet.ora looks something like this: 

/* ASO Encryption */

SQLNET.CRYPTO_SEED = “-blablablablablabla”
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (RC4_256)
SQLNET.ENCRYPTION_TYPES_CLIENT = (RC4_256)

/* ASO Checksumm */

SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (MD5)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (MD5)

After  taking this part from sqlnet.ora file  my install finished successfully.

It sounds easy but it took me some time to dig all install logs and find the cause..

Categories: Troubleshooting
Follow

Get every new post delivered to your Inbox.

Join 44 other followers