Avoid Problems on the Primary Database by Testing on a Snapshot Standby

One of the advantages of standby-first patch apply, is that I can test the patches in a production-like environment (the standby) before applying them to the primary. Should I find any issues with the patches, I can stop the process and avoid impacting the primary database.

Here’s an overview of the process.

For demo purposes, my Data Guard configuration consists of two databases:

SID: SALES
Databases: SALES_COPENHAGEN and SALES_AARHUS
Hosts: copenhagen and aarhus
Primary database: SALES_COPENHAGEN running on copenhagen

How To

This procedure starts right after I’ve patched the standby (SALES_AARHUS). It runs out of the target Oracle home, whereas the primary database (SALES_COPENHAGEN) still runs on the source Oracle home.

Convert the patched standby to a snapshot standby:
```
DGMGRL> convert database SALES_AARHUS to snapshot standby;
```
- The database sets a guaranteed restore point before opening the standby in READ WRITE mode.
Test the patch apply by running Datapatch on the standby:
```
[oracle@aarhus] $ORACLE_HOME/OPatch/datapatch
```
- One always runs Datapatch on the primary database and the changes made by the patches goes into redo to the standby.
- But, since I converted to a snapshot standby, it is now opened like a normal database and I can run Datapatch on it.
- If Datapatch completes without problems on the standby, I can be pretty sure it will do so on the primary as well. The standby is after all an exact copy of the primary database.
Optionally, perform additional testing on the standby.
- I can connect any application and perform additional tests.
- I can use SQL Performance Analyzer to check for regressing SQL statements.
- I can make changes to any data in the standby. It is protected by a restore point.
When done, convert the snapshot standby back to a physical standby:
```
DGMGRL> convert database SALES_AARHUS to physical standby;
```
- This implicitly shuts down the standby, flashes back to the restore point and re-opens the database as a physical standby.
- All changes made when it was a snapshot standby, including the Datapatch run, are undone.

Continue the patching procedure on the primary database as described elsewhere.

Is It Safe?

Sometimes, when I suggest using the standby for testing, people are like: Huh! Seriously?

What Happens If I Need to Switch Over or Fail Over?

I can still perform a switchover or a failover. However, they will take a little bit longer.

When I convert to snapshot standby:

Redo transport is still active.
Redo apply is turned off.

So, the standby receives all redo from the primary but doesn’t apply it. Since you normally test for 10-20 minutes, this would be the maximum apply lag. On a well-oiled standby, it shouldn’t take more than a minute or two to catch up.

When performing a switchover or failover on a snapshot standby, you should expect an increase with the time it takes to:

Shut down
Flashback
Apply redo

I’d be surprised if that would be more than 5 minutes. If your RTO doesn’t allow for a longer period:

Get a second standby.
Consider the reduction in risk you get when you test on the standby. Perhaps a short increase in RTO could be allowed after all.

What Happens If Datapatch Fails

If Datapatch fails on my snapshot standby, I should be proud of myself. I just prevented the same problem from hitting production.

I grab all the diagnostic information I need, so I can work with Oracle Support on the issue.
Convert back to physical standby, which will undo the failed Datapatch run.
If I expect to solve the issue quickly, leave the standby running in the target Oracle home. Otherwise, put it back into the source Oracle home.

So, yes, it’s safe to use!

Happy testing

Appendix

Zero Downtime Migration – Physical Online Migration and Testing

Testing is an essential part of any migration project of your Oracle Database. With Zero Downtime Migration (ZDM) and the Physical Online method it has become a lot easier. Before going live (i.e. doing the Data Guard switchover) you can test on your production data on your future production system – the OCI database. That’s cool.

Concept

For the duration of your test convert the OCI target database into a snapshot standby database. A short recap on snapshot standby database:

A snapshot standby database is a type of updatable standby database that provides full data protection for a primary database.

A snapshot standby database receives and archives, but does not apply, redo data from its primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the snapshot standby database cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary is then applied.

The plan is:

Build the standby database in OCI.
Migration is currently paused at ZDM_CONFIGURE_DG_SRC (-pauseafter ZDM_CONFIGURE_DG_SRC).
Convert the OCI target database – which is a physical standby – into a snapshot standby.
Do your test. Do whatever you want. The database is protected by Flashback Database, so you can insert and delete data, truncate, add tablespaces, you name it.
When you are done with your tests, convert the OCI target database back into a physical standby database. Implicitly, the database is flashed back, and now the redo is getting applied again.
After a little while the target database is now back in sync again.
Complete migration at your will.

How

You find the steps needed to conver to snapshot standby and back again below. You can repeat the process as many times as you want.

Manual

To convert the standby database to snapshot standby:

alter database recover managed standby database cancel;
shutdown immediate
startup mount
alter database convert to snapshot standby;
alter database open;

Now, the database is opened in READ WRITE mode and you can use it for testing. To convert back to a physical standby database:

shutdown immediate
startup mount
alter database convert to physical standby;
shutdown immediate
startup
alter database recover managed standby database disconnect from session;

Manual on RAC

To convert the standby database to snapshot standby:

sqlplus / as sysdba <<EOF
   alter database recover managed standby database cancel;
EOF
srvctl stop database -d $ORACLE_UNQNAME
srvctl start database -d $ORACLE_UNQNAME -o mount
sqlplus / as sysdba <<EOF
   alter database convert to snapshot standby;
   alter database open;
EOF

Now, the database is opened in READ WRITE mode and you can use it for testing. The database is only opened on one node. You can open the other nodes by executing the following on each of the nodes:

alter database open;

To convert back to a physical standby database:

srvctl stop database -d $ORACLE_UNQNAME
sqlplus / as sysdba <<EOF
   startup mount
   alter database convert to physical standby;
   shutdown immediate
EOF
srvctl start database -d $ORACLE_UNQNAME
sqlplus / as sysdba <<EOF
   alter database recover managed standby database disconnect from session;
EOF

Data Guard Broker

To convert the standby database to a snapshot standby database using broker (CDB19_fra3zt is the target DB_UNIQUE_NAME):

convert database 'CDB19_fra3zt' to snapshot standby;

Now, the database is opened in READ WRITE mode and you can use it for testing. To convert back to a physical standby database:

convert database 'CDB19_fra3zt' to physical standby;

Conclusion

One of the really cool features of ZDM is that you can use my standby database for testing in OCI – before I decide to do the switchover. You can achieve this by converting to a snapshot standby database.

Finally, a thank you to my colleague Jose Bennani Pareja for helping out with RAC database information.

Zero Downtime Migration – The Pro Tips

Here are my pro tips. It is a little mix and match of all my notes that didn’t make it into the previous blog posts but are still too good to go.

Pro Tip 1: Log Files

If something goes south where can you find the log files? On the ZDM service host:

$ZDM_BASE/chkbase/scheduled
$ZDM_BASE/crsdata/<hostname>/rhp

On the source and target hosts you can also find additional log files containing all the commands that are executed by ZDM:

$ORACLE_BASE/zdm/zdm_<db_unique_name>_<zdm_job_id>/zdm/log

Other sources:

Alert log
Data Pump process trace file DM00

Data Pump log file

Directory referenced by directory object
$ORACLE_HOME/rdbms/log/<PDB GUID>

Pro Tip 2: Troubleshooting

When you are troubleshooting it is sometimes useful to get rid of all the log files and have ZDM start all over. Some of the log files get really big and are a hard to read, so I usually stop the ZDM service, delete all the log files, and restart ZDM and my troubleshooting. But only do this if there are no other jobs running than the one you are troubleshooting:

[zdmuser@zdmhost]$ $ZDM_HOME/bin/zdmservice stop
[zdmuser@zdmhost]$ rm $ZDM_BASE/crsdata/*/rhp/rhpserver.log*
[zdmuser@zdmhost]$ rm $ZDM_BASE/chkbase/scheduled/*
[zdmuser@zdmhost]$ $ZDM_HOME/bin/zdmservice start

There are also several chapters on troubleshooting:

Pro Tip 3: Aborting A Job

Sometimes it is useful to completely restart a migration. If a database migration is already registered in ZDM, you are not allowed to specify another migration job. First, you have to abort the existing job, before you can enter a new migration job.

[zdmuser@zdmhost]$ $ZDM_HOME/bin/zdmcli abort job -jobid n

Now, you can use zdmcli migrate database command again.

Pro Tip 4: Show All Phases

A ZDM migration is split into phases, and you can have ZDM pause after each of the phases. The documentation has a list of all phases but you can also get it directly from the ZDM tool itself for a specific migration job:

[zdmuser@zdmhost]$ $ZDM_HOME/bin/zdmcli migrate database \
   -rsp ~/migrate.rsp
   ... \
   ... \
   ... \
   -listphases

Pro Tip 5: Adding Custom Scripts

You can add your own custom scripts to run before or after a phase in the migration job. You can use the -listphases command (described above) to get a list of all the phases. Then decide whether your script should run before or after that phase. This is called an action plug-in. You can bundle those together in a template to make it easier to re-use. If this is something you need, you should dive into the documentation.

If you target an Autonomous Database, you are not allowed to execute scripts on the target database host. Instead, you can .sql scripts.

The environment in which the script starts has some environment variables that you can use, like:

Database (ZDM_SRCDB)
Oracle Home (ZDM_SRCDBHOME)
ZDM Phase (RHP_PHASE)

Pro Tip 6: GoldenGate Health Check

You can use the healthcheck script on the source and target databases – where the extract and replicat process is running. It will give you invaluable information for your troubleshooting experience and it is a good idea to run and attach a health check if you need to contact My Oracle Support. It is like an AWR report but with information specific to Oracle GoldenGate replication.

Generate report by:

Installing objects in database: ogghc_install.sql
Execute health check: ogghc_run.sql
Optionally, clean-up objects: ogghc_uninstall.sql

For GoldenGate MicroServices Architecture find the scripts on the GoldenGate hub:

/u01/app/ogg/oraclenn/lib/sql/healthcheck

And run the scripts in source and target database.

Pro Tip 7: Convert From Single Instance To RAC

A useful feature of ZDM is that it can convert a single instance database to a RAC database in OCI. And it is super simple to do that. The only thing you have to do is to create the target placeholder database as a RAC database. ZDM will detect that and take care of the rest.

Finally, let me mention that if the source database is RAC One Node or RAC, then the target database must be a RAC database. Be sure to create the target placeholder database as RAC.

Pro Tip 8: Get Data Pump Log File in Autonomous Database

When you are importing into Autonomous Database, the Data Pump log file is stored in the directory DATA_PUMP_DIR. But in Autonomous Database you don’t have access to the underlying file system, so how do you get the log file? One approach is to upload the log file into Object Storage.

ZDM will create a set of credentials as part of the migration workflow. Find the name of the credentials (or create new ones using DBMS_CLOUD):

select owner, credential_name, username, enabled from dba_credentials;

Find the name of the Data Pump log file:

select * from dbms_cloud.list_files('DATA_PUMP_DIR');

Upload it. If you need help generating the URI, check the documentation:

begin
    DBMS_CLOUD.PUT_OBJECT (
       credential_name => '<your credential>',
       object_uri      => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/',
       directory_name  => 'DATA_PUMP_DIR',
       file_name       => '<file name>');
end;
/

Your OCI Console to download the Data Pump log file.

Tag: snapshot standby