This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

SharePlex vs RMAN

Hello All,

I have been working on Oracle Golden Gate quote long time but I was asked to do some POC in Shareplex for upcoming migration. 

From yesterday I'm reading Shareplex 9 with lots of documents but found lots interesting. Specifically, The below links has lots of details. 

https://gcn.com/~/media/GIG/GCN/Whitepapers/Quest_Top%205%20Reasons%20to%20Choose%20SharePlex.ashx

Unfortunately, I'm not able to find what I'm looking.

1. Is RMAN fully integrated with Shareplex ? Let say, I have multiple terabyte database which I'm looking for application migration / and database migration from one version to another version. In this case, I would like have to start RMAN backup and start with SCN like Golden Gate . 

Is it possible in shareplex ? Or only option RMAN Image backup ? If RMAN image backup then it may require downtime in source database. Is that correct ?

2.  In Golden Gate, We have option to do one time data copy. I mean initial data sync using DATA PUMP.. I can use this feature for smaller database. Is it possible from Shareplex ?

Thank you.  Babu

 

 

 

  • Hi Babu,

    SharePlex has 2 methods when starting replication. Like GoldenGate, you can start SharePlex replication from a starting SCN and use your known method for synchronization. With this method, I always feel there is a mishap waiting to be happened. With a busy database, from an SCN which is a specific location of the redo/archive_log, all open transactions has to be taken care of. If you have many long running transaction, the product has to go back in time to get the full transaction. With that said, there is a second method that SharePlex uses to start replication.

    1. Start replication - hold data in the queue file till you ready. Let say this is at 1:00PM.
    2. Instantiate your target from RMAN backup. You can recover until cancel and keep shipping log to the target and make sure the recovery is past the time of starting replication (1:00:01+). With this, you ensure that all open transactions are taken care off so no mishap.
    3. run the reconcile command on the target to remove all duplicate transaction. SharePlex can use SCN or last full log apply.

    This method has been proven since 1998 so it well full proof.

    About Datapump, you can use the same method above and use reconcile to SCN and you should be fine. There is a bit different in the SCN that you use for reconcile in this method. You will need to use SCN+1 since datapump will include the SCN for recovery while recover until in the RMAN will not.

    If you have more questions, please feel free to give us a call and we can explain in more detail.

    Thanks...
    Tom
  • Tom,

    Thank you for your detailed notes & appreciate your time.

    I'm not expected who wrote "Quest_Top 5 Reasons to Choose SharePlex" article responds my questions. Thank you again.

    By looking below response. Your saying, I can use SCN+1 from DATAPUMP and N+1[sec] for RMAN based recovery for multi-terabyte database.

    Is there any document I can go over further details ?

    This help's to show to my leadership for coming POC.

    Thank you again. Babu
  • In our documentation, SharePlex Administration guide, we have a procedure that cover this, Activate replicationwith an Oracle hot backup on active database. As you know, RMAN backup and hot backup are very similar to each other. It is about the recovery of the database. You can take a look there but I also will put the procedure here. It is much better when it is formatted.

    The database must be recover past the completion of activation for SharePlex. This is to ensure all tables is being replicated by SharePlex. You can pick a recovery point past this either through SCN or full log recovery.

    Full Log - keep shipping log over to the target and issue recover database until cancel;
    SCN recovery - keep shipping log over to the target and issue recover database until scn nnnnnnn; Just make sure that the recover point is after activation has been completed. The procedure that I included is for peer-to-peer but just ignore the reverse information and you have 1 way replication setup.



    Activate replication with an Oracle hot backup on an active database

    Use this procedure to use an Oracle hot backup to establish a target Oracle instance and activate replication without quieting the source database. This procedure involves using the reconcile command to ensure that transactions which occurred after the point of backup are applied to the target, while eliminating redundant replicated transactions that were already captured by the backup.

    Preliminary considerations
    Read these points before you proceed.

    Supported databases
    Oracle source and Oracle target
    Supported replication strategies
    All replication strategies are supported with the following limitations:

    Limitation applies to:
    Description
    Consolidated replication (many sources to one target) To establish consolidated replication, the use of a hot backup from all source systems is not possible. A backup from one source will override the data that was applied by a backup from a different source. You can use a hot backup of one of the source instances to establish a target instance, and then use another copy method to apply the objects from the other source instances. Possible methods include:

    • Export/import. For more information, see Activate replication with cold copy/transfer methods on page 264.
    • Transportable tablespaces. For more information, see Activate replication with Oracle transportable tablespaces on page 261.

    Peer-to-peer To establish peer-to-peer replication, you must:
    1. Quiet all of the systems except the trusted source system for the duration of this procedure.
    2. Move all users to the trusted source system, and then follow this procedure.
    Only after this procedure has been performed on all of the secondary systems may users may resume activity on them.
    Windows systems To use a hot backup between Windows systems, the target system must have an instance already created containing an identical ORACLE_SID and directory structure created with the Oracle creation tools. Oracle runs as a service on Windows, and the Registry entries must exist before starting the database recovery process. The database can start empty, because the hot backup will populate it.

    Requirements
    • [Unix and Linux systems] Verify that the ORACLE_SID and ORACLE_HOME in the oratab file are correct for the instance you will be establishing with the hot backup. The SID must be the SID used in the routing map in the configuration file that you will be activating.
    • Read the requirements before you start this procedure. For more information, see Requirements for activating a configuration on page 244.
    • Make certain a SharePlex database account exists in the source database (only). This account usually is created when SharePlex first is installed. See the SharePlex Installation and Setup Guide for more information.
    • Before you start, review this procedure and see the SharePlex Reference Guide for more information about the commands that are used.
    Troubleshooting
    If the configuration fails to activate, you can find information about the failure in these places:
    • Use the show log command to view the event_log.
    • View the activation process log, which is a file named SID_oconf##.log in the log sub-directory of the SharePlex variable data directory.
    See also Solve Database Setup problems on page 298.
    Procedures
    There are two procedures for activation with a hot backup, depending on your replication strategy.
    Activation with hot backup: all strategies except cascading
    Activation with hot backup: cascading replication
    Activation with hot backup: all strategies except cascading
    Use this procedure for all replication strategies except cascading replication where SharePlex will be posting to a database on the intermediary system.
    1. On the source and target systems, go to the bin sub-directory of the SharePlex product directory, and start sp_cop and sp_ctrl. In a cluster, the source is the primary node where the cluster VIP is running.
    2. On both systems, verify that the SharePlex processes are running.
    sp_ctrl> status
    3. On the target system (primary node of a target cluster), stop the Post process. This allows replicated data to accumulate in the post queue until the database has been recovered and reconciled.
    sp_ctrl> stop post
    4. On the source system, run the Oracle hot backup.
    5. When the backup is finished, activate the configuration on the source system.
    sp_ctrl> activate config filename
    6. On the source system, monitor activation status.
    NOTE: The command retains control of sp_ctrl until activation is finished.
    7. When activation is complete, switch log files on the source system.
    On-premises database:
    svrmgr1> alter system switch logfile;
    Amazon RDS database:
    Run the Amazon RDS procedure rdsadmin.rdsadmin_util.switch_logfile.
    8. Do one of the following:
    • To recover the database to a sequence number, make a note of the highest archive-log sequence number.
    • To recover the database to a Oracle System Change Number (SCN), pick an SCN to recover to on the target database.
    9. On the target system, do one of the following:
    • If recovering to a sequence number, recover the database from the hot backup using the UNTIL CANCEL option in the RECOVER clause, and cancel the recovery after Oracle has fully applied the log from the previous step.
    • If recovering to a SCN, recover the database from the hot backup using the UNTIL CHANGE scn option in the RECOVER clause, and cancel the recovery after Oracle has applied the logs matching the SCN from the previous step.
    10. On the target system, open the database with the RESETLOGS option.
    11. On the target system, run Database Setup for Oracle on the database. When prompted for the SharePlex database user, enter n to choose the existing user and password (these were copied in the backup).
    Would you like to create a new SharePlex user [y]. n
    NOTES:
    • SharePlex can remain running during the setup process.
    • Database Setup Utilities in the SharePlex Reference Guide.
    12. [Optional] If you are using named post queues and are unsure of the queue names, issue the qstatus command.
    sp_ctrl> qstatus
    13. On the target system, issue the reconcile command as follows, depending on the recovery option you chose. If you are using named post queues, issue the command for each one.
    • If recovering to a sequence number, substitute the sequence number of the log that you noted previously.
    sp_ctrl> reconcile queue queuename for datasource-datadest seq sequence_number
    Example: reconcile queue SysA for o.oraA-o.oraA seq 1234
    • If recovering to a SCN, substitute the SCN that you noted previously.
    sp_ctrl> reconcile queue queuename for datasource-datadest scn scn_number
    Example: reconcile queue SysA for o.oraA-o.oraA scn 0123456789
    NOTE: The command retains control of sp_ctrl until the reconcile process is finished.
    14. On the target system, run the cleanup.sql script to truncate the SharePlex internal tables. Instructions for running this script are in the SharePlex Reference Guide.
    15. On the target system, disable triggers on the tables, or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
    16. On the target system, disable check constraints and scheduled jobs that perform DML.
    17. [Partitioned replication only] If you are using vertically partitioned or horizontally partitioned replication for any tables, delete the unneeded columns and rows from those tables.
    18. [High availability only] On the target (secondary) system, stop Export.
    sp_ctrl> stop export
    19. [High availability and peer-to-peer only] On the target (secondary) system, activate the configuration so that SharePlex is ready in the event of failover.
    sp_ctrl> activate config filename
    20. On the target system, start the Post process. The two instances are now in synchronization, and SharePlex will continue replicating to maintain synchronization.
    sp_ctrl> start post
    21. [Optional] If this was only a partial backup, drop the tablespaces that were not copied over during the hot backup.


    With data pump, you must use flashback query for your export, flashback_scn. Once you done you export, you can substitute your import with RMAN recover and remember to use SCN+1 for reconcile with the above procedure and everything should be fine.

    As I said before, RMAN recover until SCN so it doesn't include it but FLASHBACK_SCN will include the SCN so it has to be 1 higher for reconcile.
  • thank you. I can see hot backup details in administration document.

    let me keep read and come back if any questions.

    thank you
  • Tom,

    To continue above. Few more clarification require.

    1. As per document. Its says you can using sequence # or SCN based recovery. Which mean.

    If I recover the database using sequence#.  Let say, I recovered the database till 1234. Which mean do I need to reconcile queue starting 1234 sequence Or sequence_number should be 1235?

    sp_ctrl> reconcile queue queuename for datasource-datadest seq sequence_number

    Example: reconcile queue SysA for o.oraA-o.oraA seq 1234

    l If recovering to a SCN, substitute the SCN that you noted previously.

    For SCN based recovery, I see you already informed SCN+1. I would  like to test and come back.

    sp_ctrl> reconcile queue queuename for datasource-datadest scn scn_number

    Example: reconcile queue SysA for o.oraA-o.oraA scn 0123456789

    NOTE: The command retains control of sp_ctrl until the reconcile process is finished.

    2.  The below notes tells me, Shareplex require source and target column should match ? I mean if I have few columns in source and target has 10 columns then I can't replicate ?

    I have few critical customer looking for application upgrade using Shareplex.  Which mean source database has non-partition table with 10 columns but target database has partition tables with 10 columns.  

    But your document says "Delete the unneeded columns and rows from those tables" Which mean can't map columns if I have different or new columns in target ?

    [Partitioned replication only] If you are using vertically partitioned or horizontally partitioned replication

    for any tables, delete the unneeded columns and rows from those tables.

    thanks

  • For your first question, recover database using sequence#. First we need to recover full log, no partial log recovery. In your example, you are using log 1234, then the reconcile is 1234.

    For SCN base, if you recover database until SCN then use the same number. SCN + 1 is for data pump, flashback_scn (flashback usage).

    SharePlex has the ability to column selection for replication. You can elect to replication only the columns that you want on the source. There are some items that you have to taken care of like PK/UK, constraint on the target. There might be a few more but you get the gist of it. The missing columns should not caused issue on the target. Also, column mapping is available so the column name on the source and target doesn't need to match. This tool is great for application upgrade.

    Typically, the target should have the same or more data (column/row) than the source and this is fine with SharePlex since we are name mapping everything so the sql will work fine. As I mentioned above is that the SQL that we apply should not caused any constraint error. For example, you have an extra column on the target but you have a non-null constraint on it. As we insert the data, we don't have that column since it doesn't exist on the source, the sql will failed. The solution either have a default value for that column or remove the constraint. If you follow this rule then replication will work beautifully.
  • thank you for explaining about SCN vs RMAN Recovery.  I'm good now. Its time to do the POC for RMAN 

    For PK/UK and Constraints .. I understand your requesting disable in target server. I don't think its good idea but instead of disable I can set "deferrable" mode in target system. 

    Is there any document available.  How column level mapping can be done?

    Like

    1. Target database has more column than source.  How to map in target like Golden Gate. 

    2. Let say Target system has different column structure source has. Is it possible to use any tool to map column? like defgen ? Starting from Oracle 12.1. I don't think oracle has defgen utility. I'm wondering have you done any poc? This help's for application upgrade poc. 

    3. As I said already, Source and Target database match with identical column but in target I might create new set of partition tables and rename partition table as original.  

    Example: 

    Source Table: Employee

    TARGET Table: Employee

    In Target, I will create new partition table like Employee_P and then rename Employee to Employee_Org and Employee_P to Employee 

    Note: This is an application requirement. I can't use Quest Reorg Tool. 

    In this case, Is Shareplex has intelligent to understand all tables after rename and re-create ?  

  • Maybe my answer was unclear. PK or UK should be inclusive of columns for replication or you will run into performance issue for replication (target posting).

    Unlike GoldenGate, if target has more columns than the source, there isn't anything that you have to do. By default, we will only reference the columns that define on the source. Due to this, you don't need defgen.

    About structure relating to physical vs. partitioning, it doesn't affect SharePlex since we apply SQL as normal user. Oracle will take care of the storage definition. SharePlex also have ability to replicate DDL as well which probably is off with this methodology since you want the structure to be different. By default we will replicate ALTER TABLE/TRUNCATE table so might elect not to replication TRUNCATE which is a setting with SP_OCT_REPLICATE_DDL. You can find more information on that with our documentation.

    With column mapping, it is done on the source configuration file. Unlike GoldenGate, we have one location of all configurations occurred which makes it easier to view and manage. Here is an excerpt from our manual for column mapping.

    To enforce case-sensitive column names to Oracle targets

    The Oracle Post process does not perform case conversion of column names automatically. If the case is different between source and target columns, you must use a column map to map the case of the source names to the case of the target names. To get Post to enforce the case, specify the name in its correct case and enclose it within double quotes.

    This is an example of case-sensitive column name mapping in a column map:
    Datasource o.oraA
    sales.emp(ID,"first","last") sales.emp(ID,"First","Last") sysB@o.oraB

    You might search for "column mapping" to find more detail on it.

    With SharePlex, we reference the target table by table name so when you rename it to EMPLOYEE_ORG THEN EMPLOYEE_P, we won't be able to replicate to it since it doesn't exist. Once you rename it back to EMPLOYEE, we should be able to find it fine. One thing come to mind if you want SharePlex Post applies changes to EMPLOYEE_ORG or EMPLOYEE_P, you can leverage SYNONYM to accomplish it.
  • Grant comments. Thank you.

    I will come back in 1~2 weeks with POC results.