ORA-29540: class oracle/CDC/PublishApi does not exist

ORA-29540: class oracle/CDC/PublishApi does not exist

SQL> BEGIN
2 DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
3 owner => 'cdcpub',
4 change_table_name => 'products_ct',
5 change_set_name => 'PRODUCTS_CS',
6 source_schema => 'MFCAMS',
7 source_table => 'PRODUCTS',
8 column_type_list => 'PROD_ID NUMBER(6), PROD_NAME VARCHAR2(50),PROD_LIST_PRICE NUMBER(8,2),JOB_ID VARCHAR2(10), DEPARTMENT_ID NUMBER(4)',
9 capture_values => 'both',
10 rs_id => 'y',
11 row_id => 'y',
12 user_id => 'y',
13 timestamp => 'y',
14 object_id => 'n',
15 source_colmap => 'n',
16 target_colmap => 'y',
17 options_string => 'TABLESPACE USER_DATA');
18 END;
19 /
BEGIN
*
ERROR at line 1:
ORA-29540: class oracle/CDC/PublishApi does not exist
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 611
ORA-06512: at line 2

Solutions

1.Run the sql rmcdc.sql

SQL>$ORACLE_HOMErdbms/admin/rmcdc.sql

2. Run the sql initcdc.sql

SQL>$ORACLE_HOME/rdbms/admin/initcdc.sql

ORA-29538: Java not installed

ORA-29538: Java not installed

ERROR at line 1:
ORA-29538: Java not installed
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 611
ORA-06512: at line 2

Solutions


$ORACLE_HOME/javavm/install

To load this package into the database, go to the directory location and login to SQL*Plus as the "SYS" user. Then, type the following:

SQL> spool initjvm.log
SQL> @initjvm.sql

Install and configure Java Development Kit 6 (jdk 6) on Oracle Enterprise Linux.

Download Java Development Kit 6

You can download JDK 6 versions from e.g. Sun's web site.

Select "Accept License Agreement".
Then select "Linux self-extracting file" for Linux Platform.
Save the file "jdk-6--linux-i586.bin" to e.g . /usr/local or

Install JDK or JRE

# cd /usr/local
# chmod +x jdk-6-linux-i586.bin
# ./jdk-6-linux-i586.bin
... ...
Do you agree to the above license terms? [yes or no]
yes

Press the spacebar to read Sun Microsystems, Inc. Binary Code License Agreement, then input "yes" and press Enter.
... ...
... ...
Done.

Set the environment variables

You can add the following lines to /etc/profile or .bash_profile file in your home directory. And then run "source /etc/profile" or ". .bash_profile" so that the environment variables can take effect.


export JAVA_HOME=/usr/local/jdk1.6.0
export PATH=$JAVA_HOME/bin:$PATH

Check the JDK version

# java -version
java version "1.6.0"
Java(TM) SE Runtime Environment (build 1.6.0-b105)
Java HotSpot(TM) Client VM (build 1.6.0-b105, mixed mode, sharing)

Transprotable Tablespace using export/import

Transprotable Tablespace using export/import

Source

1. Create tablespace.

SQL> CREATE TABLESPACE TT_DATA
DATAFILE 'F:\ORACLE\ORADATA\ORCL\TT_DATA01.ORA' SIZE 50M;

SQL> CREATE TABLESPACE TT_INDEX
DATAFILE 'F:\ORACLE\ORADATA\ORCL\TT_INDEX01.ORA' SIZE 50M;


2. Create User.

SQL> create user ttbs identified by ttbs default tablespace tt_data temporary tablespace temp;

SQL> grant connect, resource to ttbs;

SQL> alter user ttbs quota unlimited on tt_index;


SQL> select name from v$tablespace where name like 'TT_%';

NAME
------------------------------
TT_DATA
TT_INDEX


SQL> select tablespace_name,plugged_in,status from dba_tablespaces
where tablespace_name like 'TEST%';


TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA NO ONLINE
TT_INDEX NO ONLINE


3. Create table and index

SQL> create table ttbs.testtable (sno number, name varchar2(10)) tablespace tt_data;

SQL> insert into ttbs.testtable values (1001, 'Doyensys');

SQL> commit;

SQL> create index ttbs.i_testtable on ttbs.testtabale(sno) tablespace tt_index;



SQL> select owner,count(*) from dba_segments
where tablespace_name in ('TT_DATA','TT_INDEX') group by owner;

OWNER COUNT(*)
------------------------------ ----------
TTBS 2

SQL> select tablespace_name,count(*) from dba_segments
where owner='TTBS' group by tablespace_name;

TABLESPACE_NAME COUNT(*)
------------------------------ ----------
TT_INDEX 1
TT_DATA 1


SQL> select segment_type,count(*) from dba_segments
where owner='TTBS' group by segment_type;

SEGMENT_TYPE COUNT(*)
------------------ ----------
INDEX 1
TABLE 1


4.Source tablespace change read-only mode.

SQL>alter tablespace TT_DATA read only;

SQL>alter tablespace TT_INDEX only;


SQL> execute dbms_tts.transport_set_check('TT_DATA,TT_INDEX', TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected


select file_name from dba_data_files where tablespace_name in ('TT_DATA','TT_INDEX');


SQL> select file_name from dba_data_files
where tablespace_name in ('TT_DATA','TT_INDEX');

FILE_NAME
--------------------------------------------------------------------------------

F:\ORACLE\ORADATA\ORCL\TT_DATA01.ORA
F:\ORACLE\ORADATA\ORCL\TT_INDEX01.ORA


5. Export the metadata using transportable tablespace

exp "'/ as sysdba'" file=exp_tts.dmp log=exp_tts.log transport_tablespace=y tablespaces=TT_DATA,TT_INDEX statistics=none


6. Copy TT_DATA01.ORA,TT_INDEX01.ORA datafiles and exp_tts.dmp to target server.

7 . Once copy is complete,Put source tablespace read-write mode.

SQL> alter tablespace TT_DATA read write;

SQL> alter tablespace TT_INDEX read write;



Target


7. create user.

create user ttbs identified by ttbs default tablespace user_data temporary tablespace temp;

grant connect, resource to ttbs;

8. import metadata


imp "'/ as sysdba'" file=exp_tts.dmp log=imp_tts.log fromuser=ttbs touser=ttbs transport_tablespace=y datafiles=F:\oracle\oradata\orcl\TT_DATA01.ORA,F:\oracle\oradata\orcl\TT_INDEX01.ORA


SQL>select tablespace_name,plugged_in,status from dba_tablespaces
where tablespace_name like 'TEST%';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA YES READ ONLY
TT_INDEX YES READ ONLY

9. target tablespace put read-write mode.

SQL> alter tablespace TT_DATA read write;

SQL> alter tablespace TT_INDEX read write;


SQL> select tablespace_name,plugged_in,status from dba_tablespaces
2 where tablespace_name like 'TEST%';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA YES ONLINE
TT_INDEX YES ONLINE


SQL> select owner,count(*) from dba_segments
where tablespace_name in ('TT_DATA','TT_INDEX') group by owner;

OWNER COUNT(*)
------------------------------ ----------
TTBS 2

SQL> select tablespace_name,count(*) from dba_segments
where owner='TTBS' group by tablespace_name;

TABLESPACE_NAME COUNT(*)
------------------------------ ----------
TT_INDEX 1
TT_DATA 1


SQL> select segment_type,count(*) from dba_segments
where owner='TTBS' group by segment_type;

SEGMENT_TYPE COUNT(*)
------------------ ----------
INDEX 1
TABLE 1

Create Recovery catalog using RMAN

Create Recovery catalog using RMAN


1. Create tablespace for Catalog

CREATE TABLESPACE rman_data
DATAFILE '/u01/oracle/product/oradata/orcl/rman_data01.dbf' SIZE 50M;

2. Create user for RMAN

CREATE USER rman
IDENTIFIED BY rman
DEFAULT TABLESPACE rman_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON rman_data;

3. Grant user.

GRANT create session TO rman;
GRANT recovery_catalog_owner TO rman;

4. create catalog

rman catalog rman/rman

RMAN> create catalog tablespace rman_data;


5. Register target database.

rman target / catalog rman/rman@rman_cat_db

RMAN> register database;

ORA-29341: The transportable set is not self-contained

Transportable tablespace using export of index tablespace given error

H:\oracle\oradata\doyen\back_test_tt>exp "'/ as sysdba'" file=TT_INDEX.dmp
log=TT_INDEX.log transport_tablespace=y tablespaces=TT_INDEX statistics=none

Export: Release 10.2.0.3.0 - Production on Wed Oct 21 12:24:06 2009

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 29341 encountered
ORA-29341: The transportable set is not self-contained
ORA-06512: at "SYS.DBMS_PLUGTS", line 1387
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully


Solutions:


SQL> execute dbms_tts.transport_set_check('TT_INDEX', TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

ORA-27054: NFS file system where the file is created or resides

Starting backup at 21-OCT-09
no parent backup or copy of datafile 18 found
channel c1: starting datafile copy
input datafile fno=00018 name=/home/oracle/product/10.2.0/oradata/orcl/kkk.dbf
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c1 channel at 10/21/2009 21:26:48
ORA-19504: failed to create file "/data/files/kkk.dbf"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3


Solutions

Linux

Add the following entry in /etc/fstab

hard,rw,noac,rsize=32768,wsize=32768,suid,proto=tcp,vers=3

Oracle Application Express enable SSL

Oracle Apex configure SSL

Oracle Application Express enable SSL (http to https)

Goto Application Server
$ORACLE_HOME/opmn/conf/opmn.xml

find for ssl-disabled
change to ssl-enabled
Save opmn.xml

Add the entry in httpd.conf file.

RewriteEngine On
RewriteCond %{SERVER_PORT} !^443$
RewriteRule ^/pls/apex/(.*)$ https://example.com/pls/apex/$1 [L,R]

Apache2.0 proxy for Oracle Apex

Apache2.0 proxy for Oracle Apex


User => HTTP2.x => OHS => APEX

Add the entry in httpd.conf file.

ProxyPass /apex http://192.168.5.25:8080/apex
ProxyPassReverse /apex http://192.168.5.25:8080/apex
ProxyPass /i http://192.168.5.25/i
ProxyPassReverse /i http://192.168.5.25/i

Oracle Streams SET_KEY_COLUMNS

Oracle Streams SET_KEY_COLUMNS

When an apply process applies changes to a table, substitute key columns can either replace the primary key columns for a table that has a primary key or act as the primary key columns for a table that does not have a primary key. Set the substitute key columns for a table using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. This setting applies to all of the apply processes that apply local changes to the database.


For Example,

Source EMP table have only one primary column.but i need target table two primay column(emp_id,worker_id).

BEGIN
DBMS_APPLY_ADM.SET_KEY_COLUMNS(
object_name => 'SCOTT.EMP',
column_list => 'EMP_ID,WORKER_ID');
END;
/


Note: You must specify an unconditional supplemental log group at the source database for all of the columns specified as substitute key columns in the column_list or column_table parameter at the destination database

Oracle Streams Delete Column

Oracle Streams Delete Column.

Source Schame Name : SCOTT

Source Table Name : EMP

Target Schame Name : SCOTT

Target Table Name : EMP

Delete Column Names : SAL,JOB

Two Steps.

1.Rule-based transformation.
2.Rename columns in the LCR using a DML Apply Handler.


1.Rule-based transformation.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.EMP',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/

SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='EMP';


begin
DBMS_STREAMS_ADM.DELETE_COLUMN(
rule_name => 'STRMADMIN.EMP34',
table_name => 'SCOTT.EMP',
column_name => 'SAL',
operation => 'ADD');
END;
/

begin
DBMS_STREAMS_ADM.DELETE_COLUMN(
rule_name => 'STRMADMIN.EMP34',
table_name => 'SCOTT.EMP',
column_name => 'JOB',
operation => 'ADD');
END;
/


2.Rename columns in the LCR using a DML Apply Handler.

rem Create DML handler procedure

CREATE OR REPLACE PROCEDURE emp_dml_handler(in_any IN SYS.ANYDATA)
IS

lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
dmlcommand VARCHAR2(10);
row_empno SYS.ANYDATA;
v_empno NUMBER;

BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
dmlcommand := lcr.GET_COMMAND_TYPE();


-- Filter out required row and and columns

IF object_owner = 'SCOTT' and
object_name = 'EMP' and
dmlcommand IN ('INSERT','UPDATE','DELETE') THEN

-- Remove Columns
lcr.delete_column('SAL','*');
lcr.delete_column('JOB','*');

LCR.EXECUTE(TRUE);
END IF;
END;
/

rem Set the DML Handler for the INSERT operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/


rem Set the DML Handler for the UPDATE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the DELETE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/

Oracle Streams Rename Column

Oracle Streams Rename Column.

Source Schame Name : EMP

Source Table Name : MANAGER

Column Names : MANAGER_ID,MANAGER_NAME,EMPLOYEE

Target Schame Name : EMP

Target Table Name : MANAGER

Column Names : MANAGER_ID,MANAGER_NAME,WORKER

Two Steps.

1.Rule-based transformation.
2.Rename columns in the LCR using a DML Apply Handler.


1.Rule-based transformation.

Create Capture rule.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'EMP.MANAGER',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/

SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='MANAGER';


BEGIN
DBMS_STREAMS_ADM.RENAME_COLUMN(
rule_name => 'MANAGER33',
table_name => 'EMP.MANAGER',
from_column_name => 'EMPLOYEE',
to_column_name => 'WORKER',
value_type => 'NEW',
step_number => 0,
operation => 'ADD');
END;
/


2.Rename columns in the LCR using a DML Apply Handler.

CREATE OR REPLACE PROCEDURE rename_column_EMPLOYEE (in_any in sys.anydata)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(30);
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();

-- Filter out required owner and table name
IF lcr.get_object_owner() = 'EMP' AND lcr.get_object_name() = 'MANAGER' THEN
IF (lcr.get_value('NEW','EMPLOYEE') is not null) THEN
lcr.rename_column('EMPLOYEE','WORKER','NEW');
END IF;
IF (lcr.get_value('OLD','EMPLOYEE') is not null) THEN
lcr.rename_column('EMPLOYEE','WORKER','OLD');
END IF;
LCR.EXECUTE(TRUE);
END IF;
END;
/


rem Set the DML Handler for the INSERT operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the UPDATE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the DELETE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/

Oracle Streams Rename Schema

Oracle Streams Rename Schema.

Source Schame Name : EMP

Target Schame Name : WORKER


Create Capture rule.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'EMP.MANAGER',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/



SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='MANAGER';


BEGIN
DBMS_STREAMS_ADM.RENAME_SCHEMA(
rule_name => 'STRMADMIN.MANAGER35',
from_schema_name => 'EMP',
to_schema_name => 'WORKER',
step_number => 0,
operation => 'ADD');
END;
/

Oracle Streams Add New Column in Target Table

Oracle Streams Add New Column in target Table



CREATE OR REPLACE PROCEDURE test_im_amc(evt IN SYS.ANYDATA)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
BEGIN
-- Access the LCR. RC holds the return code.
rc := evt.GETOBJECT(lcr);
--add a new column called commit_scn
lcr.ADD_COLUMN('new','COMMIT_SCN',SYS.AnyData.ConvertNumber(lcr.GET_SCN()));
-- Apply row LCR
lcr.EXECUTE(true);
END;


BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the UPDATE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the DELETE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/