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...)
Comments
Post a Comment