We have active-passive database replication in our system cluster, so passive db belongs to DR class of nodes. We're trying to design zero-downtime deployment so I went out with few ideas.
Since we have 2 databases schemas, is it possible to replicate only one and second one (which is not replicated) to use in full read-write mode?
Data guard is used to replicate data. In previous company, we did such thing easily with MySQL (free edition), but not sure is it possible with Oracle. For me, if MySQL can do it, Oracle can as well, but internet is not providing me the exact answer on it.
I read somewhere that it's possible to do some EXCLUDE
commands and to limit what is going to be replicated, but need to use that excluded schema in full read-write mode.
Thanks
Best Answer
If you want to do replication from one database to another, but skip certain schemas, then a Data Guard logical standby database may be a good fit for you.
However, if you want to perform replication from within the same database at the schema level, then Oracle GoldenGate "may" be able to do that... however, I've never used GoldenGate, so I can't confirm if that would be a good fit.
This answer assumes you want to do replication from one database to another, but skip certain schemas.
A logical standby database uses logminer and SQL Apply to replay the SQL statements on the standby database. You can have a very fine grained control over what is replicated and what isn't; however, administering a logical standby is far more complicated than administering a physical standby, therefore, if you aren't familiar with Data Guard, I would become very familiar with administering a physical standby before attempting to administer a logical standby database. In addition, a logical standby is open in read/write, and you can control what objects can and can't be modified directly by turning off SQL Apply for those objects (or entire schemas), and setting GUARD to standby.
1) Create a Data Guard physical standby
2) Convert a physical standby to a logical standby
3) Skip replication (DDL and DML) for an entire schema (assuming HR is the schema you wish to skip)
4) Allow changes to objects not being maintained through SQL Apply
The 12c docs on Data Guard logical standby databases can be found here:
Creating a logical standby database
Managing a logical standby database