12c and streams (and lack of CDC)



This is a 2 part post - but they are essentially standalone (and i haven't actually written or tested any of part 2.....)

So some background, we plan to upgrade out 9.2.0.4 database (yes i know....) to 12.1.0.1 as the vendor now supports this as long as we do an application upgrade at the same time - that's all fine and we're working on ways to minimize the outage for that.

However we have some issues with 12c - namely the fact that oracle have decided to kill CDC (that's change data capture for those of you not familiar with it) - we use this as a trickle feed method into one of our warehouse systems - so this is very annoying - it means the interface has to change.

The warehouse is being replaced in a year or so which means we don't want to spend a huge effort changing the thing - but we have to change something as the current code won't work.

Our idea is to use streams (which is just about hanging on in 12c) to replicate the tables in question to the 10g (yes i know - but it's not as bad as 9i) warehouse and then build a CDC capture on the streams replicated tables - this sounds fine in theory and it minimizes the change to the warehouse (should just be a repoint of the cdc link) - but we'll see how well it works in part 2.

However for part 1 i just wanted to prove the process of setting up a streams replication of a single table from 12c to 10g.

It still annoys me that streams is 'marked for death' (or deprecated as it's more commonly known) as i think it's really neat and I'm still not that much of a fan of goldengate.

Anyway here is a simple demo - this should essentially work in any version post 9i i think (streams was just about there at the end of 9i from what i remember but was pretty ropy).

OK - lets do the demo - in this case i have a 12c database called Uresh (sorry Uresh i borrowed this as it was the right version and not being used...) and a 10g databases called Dummy that i want to replicate a table to.

Lets cover off some of the basic streams setup bits.

First of all you need a streams admin db account in both databases - which is created like this

SQL>  create user strmadmin identified by strmadmin;

User created.

SQL>  grant dba to strmadmin;

Grant succeeded.


SQL> BEGIN
  DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee          => 'strmadmin',
    grant_privileges => TRUE);
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL>


DBA isn't strictly necessary but I'm taking the easy option, the plsql at the end grants some specific explicit rights on some sys objects which you don;t get via the role.

Next i create a demo user and table that i want to replicate (just in the source db of course - Uresh in this case)

SQL> create user demo identified by demo;

User created.

SQL> grant resource,unlimited tablespace to demo;

Grant succeeded.

SQL> create table demo.trades(id number, trade_date date);

Table created.

SQL> alter table demo.trades add primary key (id);

Table altered.

SQL>  alter table demo.trades add supplemental log data (all) columns;

Table altered.

SQL>


The supplemental logging bit is a bit of a long topic - i'll let you read up on that seperately - suffic to say you need some degree os supplemental logging for streams

We then need to create a directory object (again both sides for later use by datapump)

SQL> create directory tmp as '/tmp'; -- on both sides

Directory created.


 ALso for later use we need to define database links between both databases

source->destination for streams to use for queue to queue propagation
destination->source for the datapump instantiation steps

So to do that we

SQL> connect strmadmin/strmadmin
Connected.
SQL> create database link dummy connect to strmadmin identified by strmadmin using 'DUMMY';

Database link created.

SQL> select sysdate from dual@dummy;

SYSDATE
---------
19-FEB-15


which works fine 12c->10g

However when we try the other way we get

SQL> connect strmadmin/strmadmin

Connected.
SQL> create database link uresh connect to strmadmin identified by strmadmin using 'URESH';

Database link created.



SQL>  select sysdate from dual@uresh;
 select sysdate from dual@uresh
                          *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from URESH


It seems that the db link is changing the password (i.e. losing the case sensitivity) - so we need to disable case sensitive passwords in 12c


SQL> alter system set sec_case_sensitive_logon=FALSE;

System altered.


Now we try it again

SQL>  select sysdate from dual@uresh;

SYSDATE
---------
19-FEB-15


And all is good

Now we have the building blocks in place and we can run the helper procedures to set up streams for us - rather than working out all the plsql for ourselves

So i build this block of code

begin
DBMS_STREAMS_ADM.MAINTAIN_TABLES(
   table_names                  =>'DEMO.TRADES',
   source_directory_object      =>'TMP',
   destination_directory_object  =>'TMP',
   source_database               =>'URESH',
   destination_database          =>'DUMMY',
   perform_actions              =>FALSE,
   script_name                  =>'setup.sql',
   script_directory_object      =>'TMP',
   dump_file_name               =>'setup.dmp',
   capture_name                 =>'CDC_CAPTURE',
   propagation_name             =>'CDC_PROP',
   apply_name                   =>'CDC_APPLY',
   log_file                     =>'setup.log',
   bi_directional               =>FALSE,
   include_ddl                  =>FALSE,
   instantiation                =>DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK);
end;
/


It's reasonably self explanatory - the key bit here is that perform_actions is set to FALSE - so instead of executing anything - it will create a file called setup.sql with all the plsql needed to build everything - so we can go and have a look what it will do (if you are so inclined - it's useful as it shows how to manually set things up)

Now lets run it with perform_actions set to TRUE (if we run it via the script it created we get prompted for loads of stuff so lets be lazy again)

And i get this

begin
*
ERROR at line 1:
ORA-23616: Failure in executing block 4 for script
0F744F8A022E3AC9E0530200007F44EF with
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 659
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 682
ORA-06512: at "SYS.DBMS_STREAMS_MT", line 8003
ORA-06512: at "SYS.DBMS_STREAMS_ADM_IVK", line 2581
ORA-06512: at "SYS.DBMS_STREAMS_ADM_IVK", line 2648
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 824
ORA-06512: at line 2


OK - simple enough to resolve - the source must of course be in archivelog mode

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     374
Current log sequence           375

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2296576 bytes
Variable Size             826279168 bytes
Database Buffers          234881024 bytes
Redo Buffers                5480448 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>


So now we are ready to try again


begin
DBMS_STREAMS_ADM.MAINTAIN_TABLES(
   table_names                  =>'DEMO.TRADES',
   source_directory_object      =>'TMP',
   destination_directory_object  =>'TMP',
   source_database               =>'URESH',
   destination_database          =>'DUMMY',
   perform_actions              =>TRUE,
   script_name                  =>'setup.sql',
   script_directory_object      =>'TMP',
   dump_file_name               =>'setup.dmp',
   capture_name                 =>'CDC_CAPTURE',
   propagation_name             =>'CDC_PROP',
   apply_name                   =>'CDC_APPLY',
   log_file                     =>'setup.log',
   bi_directional               =>FALSE,
   include_ddl                  =>FALSE,
   instantiation                =>DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK);
end;
/

begin
*
ERROR at line 1:
ORA-23616: Failure in executing block 5 for script
0F744F8A022E3AC9E0530200007F44EF with
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_RECO_SCRIPT_INVOK", line 130
ORA-06512: at "SYS.DBMS_STREAMS_RPC", line 358
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 659
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 682
ORA-06512: at "SYS.DBMS_STREAMS_MT", line 2440
ORA-06512: at "SYS.DBMS_STREAMS_MT", line 7787
ORA-06512: at "SYS.DBMS_STREAMS_ADM_IVK", line 2581
ORA-06512: at "SYS.DBMS_STREAMS_ADM_IVK", line 2648
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 824
ORA-06512: at line 2


And we fail again with some random error - lets see if the view related to this tells us any more

SQL> select * from dba_recoverable_script_errors;

SCRIPT_ID                         BLOCK_NUM ERROR_NUMBER
-------------------------------- ---------- ------------
ERROR_MESSAGE
--------------------------------------------------------------------------------
ERROR_CRE
---------
0F744F8A022E3AC9E0530200007F44EF          5       -39001
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_RECO_SCRIPT_INVOK", line 130
ORA-06512: at "SYS.DBMS_STREAMS_RPC", line 358
19-FEB-15


ANd it doesn't......

Lets just try it again - second time lucky and all that....

begin
DBMS_STREAMS_ADM.MAINTAIN_TABLES(
   table_names                  =>'DEMO.TRADES',
   source_directory_object      =>'TMP',
   destination_directory_object  =>'TMP',
   source_database               =>'URESH',
   destination_database          =>'DUMMY',
   perform_actions              =>TRUE,
   script_name                  =>'setup.sql',
   script_directory_object      =>'TMP',
   dump_file_name               =>'setup.dmp',
   capture_name                 =>'CDC_CAPTURE',
   propagation_name             =>'CDC_PROP',
   apply_name                   =>'CDC_APPLY',
   log_file                     =>'setup.log',
   bi_directional               =>FALSE,
   include_ddl                  =>FALSE,
   instantiation                =>DBMS_STREAMS_ADM.INSTANTIATION_TABLE);
end;
/



begin
*
ERROR at line 1:
ORA-23622: Operation SYS.DBMS_STREAMS_ADM.MAINTAIN_TABLES is in progress.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_STREAMS_MT", line 2626
ORA-06512: at "SYS.DBMS_STREAMS_MT", line 7752
ORA-06512: at "SYS.DBMS_STREAMS_ADM_IVK", line 2581
ORA-06512: at "SYS.DBMS_STREAMS_ADM_IVK", line 2648
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 824
ORA-06512: at line 2


So no joy - we need to kill this step to be able to try again

To do that we find the script name and run som plsql

SQL> select * from DBA_RECOVERABLE_SCRIPT ;
0F744F8A022E3AC9E0530200007F44EF 19-FEB-15 SYS                                                                                                                  DBMS_STREAMS_ADM                                                                                                                  MAINTAIN_TABLES              STRMADMIN                                                                                                                 ERROR                  11          4



SQL> exec DBMS_STREAMS_ADM.RECOVER_OPERATION('0F744F8A022E3AC9E0530200007F44EF','ROLLBACK');

PL/SQL procedure successfully completed.


Now we try again

SQL> begin
  2  DBMS_STREAMS_ADM.MAINTAIN_TABLES(
  3     table_names                  =>'DEMO.TRADES',
  4     source_directory_object      =>'TMP',
  5     destination_directory_object  =>'TMP',
  6     source_database               =>'URESH',
  7     destination_database          =>'DUMMY',
  8     perform_actions              =>TRUE,
  9     script_name                  =>'setup.sql',
 10     script_directory_object      =>'TMP',
 11     dump_file_name               =>'setup.dmp',
 12     capture_name                 =>'CDC_CAPTURE',
 13     propagation_name             =>'CDC_PROP',
 14     apply_name                   =>'CDC_APPLY',
 15     log_file                     =>'setup.log',
 16     bi_directional               =>FALSE,
 17     include_ddl                  =>FALSE,
 18     instantiation                =>DBMS_STREAMS_ADM.INSTANTIATION_TABLE);
 19  end;
 20  /

PL/SQL procedure successfully completed.

SQL>


So looks good - lets check....

lets insert some data

SQL> insert into demo.trades values (1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL>


and now look on the remote site

SQL> select * from demo.trades;
select * from demo.trades
                   *
ERROR at line 1:
ORA-00942: table or view does not exist


right....... so no errors but it failed miserable - lets check the datapump logs - and on the remote side we see

Master table "STRMADMIN"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "STRMADMIN"."SYS_IMPORT_TABLE_01":
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
ORA-39083: Object type PROCACT_INSTANCE failed to create with error:
ORA-31625: Schema DEMO is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate(object_owner=>'DEMO', object_name=>'TRADES', object_type=>'2', export_
db_name=>'URESH', inst_scn=>'11497749', ignore_scn=>'11496215');COMMIT; END;
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39112: Dependent object type TABLE:"DEMO"."TRADES" skipped, base object type  creation failed
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"DEMO"."SYS_C004759" skipped, base object type TABLE:"DEMO
"."TRADES" creation failed
ORA-39112: Dependent object type CONSTRAINT:"DEMO"."SYS_C004760" skipped, base object type TABLE:"DEMO
"."TRADES" creation failed
ORA-39112: Dependent object type CONSTRAINT:"DEMO"."SYS_C004779" skipped, base object type TABLE:"DEMO
"."TRADES" creation failed
ORA-39112: Dependent object type CONSTRAINT:"DEMO"."SYS_C004780" skipped, base object type TABLE:"DEMO
"."TRADES" creation failed
ORA-39112: Dependent object type CONSTRAINT:"DEMO"."SYS_C004778" skipped, base object type TABLE:"DEMO
"."TRADES" creation failed
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"DEMO"."SYS_C004759"
 creation failed
Job "STRMADMIN"."SYS_IMPORT_TABLE_01" completed with 8 error(s) at 16:39:21


Now this is because we replicated just a table - if we'd chosen the whole schema then datapump would have created it for us.

so lets manually create the user

SQL> create user demo identified by demo;

User created.

SQL> grant resource,unlimited tablespace to demo;

Grant succeeded.


SO now we have the system in a kind of half setup state - the easiest way to deal with it in this case is to just drop strmadmin both sides which will clean everything up

SQL> drop user strmadmin cascade;

User dropped.

SQL> create user strmadmin identified by strmadmin;

User created.

SQL> grant dba to strmadmin;

Grant succeeded.

SQL> BEGIN
  DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee          => 'strmadmin',
    grant_privileges => TRUE);
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL>


We then put the db links back as before and try the whole thing again. Now the first time i ran this again i got the same error as i did earlier and I've decided the root cause is that the file setup.sql must not exist before you run the process again - so i re-dropped everything and did it all again

now when i run

SQL> begin
  2  DBMS_STREAMS_ADM.MAINTAIN_TABLES(
  3     table_names                  =>'DEMO.TRADES',
  4     source_directory_object      =>'TMP',
  5     destination_directory_object  =>'TMP',
  6     source_database               =>'URESH',
  7     destination_database          =>'DUMMY',
  8     perform_actions              =>TRUE,
  9     script_name                  =>'setup.sql',
 10     script_directory_object      =>'TMP',
 11     dump_file_name               =>'setup.dmp',
 12     capture_name                 =>'CDC_CAPTURE',
 13     propagation_name             =>'CDC_PROP',
 14     apply_name                   =>'CDC_APPLY',
 15     log_file                     =>'setup.log',
 16     bi_directional               =>FALSE,
 17     include_ddl                  =>FALSE,
 18     instantiation                =>DBMS_STREAMS_ADM.INSTANTIATION_TABLE);
 19  end;
 20  /

PL/SQL procedure successfully completed.

SQL>


i get no errors (and there are no errors with datapump either.

Lets now do another check - on the source side

SQL> select * from demo.trades;

        ID TRADE_DAT
---------- ---------
         1 19-FEB-15

SQL> insert into demo.trades values (2,sysdate);

1 row created.

SQL> commit;

Commit complete.


And the destination - there is a tiny delay then the new row appears!

SQL> select * from demo.trades;

        ID TRADE_DAT
---------- ---------
         1 19-FEB-15

SQL> /

        ID TRADE_DAT
---------- ---------
         1 19-FEB-15

SQL> /

        ID TRADE_DAT
---------- ---------
         1 19-FEB-15
         2 19-FEB-15


So it's all working - and it is pretty easy to be honest once you get your head round the steps,

SO there you go simple streams replication of one table.

Now i just need to do part 2 with CDC - and i need to start from scratch as last time i set it up was about 5 years ago......

Update tomorrow (maybe...)















0 comments:

Post a Comment