WARNING: This web is not used anymore. Please use PDBService.3DStreamsRecovery instead!
 

Backup & Recovery Test for 3D Streams Setup

Scenarios to be tested (proposal by Gordon)

As described in the 3D meeting, here are the scenarios we should look at for database recovery testing and how it affects Oracle Streams:

1) A Tier-1 database crashes and data is lost. A point-in-time recovery can be done, but this does not include the latest data from the master at Tier-0

2) A Tier-1 database crashes and all data is lost.

3) Data was "missed" between Tier-0 and Tier-1 for a certain amount of time (i.e. data before a certain time was replicated, then data was missed and then further data was replicated)

4) Tier-0 database crashes and is restored to a point-in-time before the latest data that was sent to Tier-1 databases (i.e. Tier-0 is now behind Tier-1)

5) Due to user/data error we (all) are required to roll back to a point-in-time

Scenario number 3 may not be possibly (i.e. streams would detect this) but we should make sure this is true and document it.

The next stages:

  • Co-ordinate tests between sites/experiments who are willing to take part
  • I will contact Eva to see start looking at what we think streams will do in these scenarios and how we can recover

Recovery Scenarios and Oracle Streams

Scenario 1. Point-in-time recovery on one destination database:

We will need to re-apply the captured changes that had already been applied after the point-in-time recovery.

The Streams documentation describes two possible solutions, but as there will be more destination sites and we do not want to impact them, the best solution is:

  • split the Streams setup [ @ master database ] in order to create a new capture process/propagation job to manage the changes that must be re-reapplied at the destination database. The capture process must start in the last SCN applied at the destination.
  • general Streams setup will work as usual between the source database and the other destination databases
  • merge both Streams setups on the general one
Split/Merge solution has been successfully tested in the case of one destination site is down.

It may be necessary to get old archive logs back to disk from the backups.

More information on: Oracle Streams Replication Administrator's Guide. 9. Performing Point-in-Time Recovery in a Streams Environment

Scenario 2. Tier-1 database crashes and all data is lost:

Backup restore + database recovery. Back to Scenario 1.

Scenario 3. Data was "missed" between Tier-0 and Tier-1 for certain amount of time:

Streams replication should avoid this situation considering Streams definition.

Scenario 4. Point-in-time recovery on the source database:

There are 2 possibilities:

1. The point-in-time recovery SCN is less than the highest apply SCN

In this case, we need to know if the transactions applied at the destination database after the point-in-time recovery can be identified so we can execute these transactions at the source database (without being replicated, only to synchronize the databases)

2. The point-in-time recovery SCN is greated that the highest apply SCN

The capture process must re-capture the changes between the oldest apply SCN and the point-in-time recovery

More information on: Oracle Streams Replication Administrator's Guide. 9. Performing Point-in-Time Recovery in a Streams Environment

Scenario 5. Point-in-time recovery on the source and on the destinations:

I assume that the point-in-time will be the same for all the databases. In this case, the easiest will be re-create the capture and apply processes using the same rule sets in order to reset the SCN.

Scenario 6. Tablespace Point-in-time recovery on the source database:

Tablespace is recover in an auxiliary instance so the schema or the table/s are available on this auxiliary instance.
  • Stop propagation jobs to the destination sites.
  • Export schema or table/s from the auxiliary instance.
  • Import schema or table/s on the source database and on thedestinations.
  • Re-enable propagation jobs to the destination sites.
In this case it may even be possible to use an export file from one of the destination sites. I suppose it will depend on the error which causes the recovery need.

Scenario 7. Tablespace Point-in-time recovery on the destination database:

From my point of view, the easiest is to use the schema or table/s export file from the source database (or from another destination so it will not impact the source database).
  • Stop the propagation job to the destination site.
  • Export schema or table/s.
  • Import schema or table/s on the destination database.
  • Re-enable propagation job to the destination site.

Database Recovery Tests with Oracle Streams: description

Scenario 1. Point-in-time recovery on one destination database:

Case Test: Table STREAMS_TRHOUGHPUT.APL10 was accidentally dropped by one user.

Let's simulate the loss of the table as in the following manner:

   SQL> SET TIME ON
   SQL> DROP TABLE streams_throughput.apl10;
Note the date/time the table has been dropped and shutdown the database.

Steps to perform the recovery:

  1. [source database] Stop ( and note select PROPAGATION_NAME, RULE_SET_NAME from dba_propagation;) and drop the propagation job to the destination site.
  2. Execute RMAN script to perform database restore and recovery until the specified time.
  3. Query the oldest message number for the apply process: select apply_name, oldest_message_number from dba_apply_progress;
  4. [source database] Create a new streams queue.
  5. [source database] Create the new propagation job, specifying rule sets used by the original propagation.
  6. [source database] Create the new capture process, specifying rule sets used by the original capture process (select CAPTURE_NAME, RULE_SET_NAME from dba_capture;). Set the start_scn parameter to the value recorded from the query in step 3.
  7. Start the capture process.
  8. Query select apply_name, oldest_message_number from dba_apply_progress;
  9. [source database] Query select capture_name, capture_message_number from v$streams_capture; (orginal capture)
  10. When the oldest message number of the apply process is approaching the capture number of the original capture process at the source database, then stop the original capture process.
  11. When the oldest message number of the apply process is beyond the capture number of the original capture process, then stop the new capture process created in step 6.
  12. Merge both Streams setups (drop new capture process, stop and drop new propagation and queue, and recreate original propagation) and start the original capture process.

Scenario 2. Tier-1 database crashes and all data is lost:

Let's simulate the loss of one datafile / several datafiles.
   SQL> SHUTDOWN IMMEDIATE;
Delete one / several datafile/s. Start the database. We will get an error.

Steps to perform the recovery:

  • (1) Connect to RMAN
  • (2) Take the file/s offline and open the database:
   RMAN> SQL 'ALTER DATABASE DATAFILE x OFFLINE';
   RMAN> OPEN DATABASE;
  • (3) Restore the datafile/s:
   RMAN> RESTORE DATAFILE x;
  • (4) Recover the datafile/s:
   RMAN> RECOVER DATAFILE x;
  • (5) Take the file/s online:
   RMAN> SQL 'ALTER DATABASE DATAFILE x ONLINE';
  • (6) Back to scenario 1 (except step number 2).

Scenario 4. Point-in-time recovery on the source database:

Case Test: Table STRMUSER.TEST was accidentally dropped by one user.

Let's simulate the loss of the table as in the following manner:

   SQL> SET TIME ON
   SQL> DROP TABLE strmuser.test;
Note the date/time the table has been dropped and shutdown the database.

Steps to perform the recovery:

  • (1) Perform the point-in-time recovery on the source database: execute RMAN script to perform database restore and recovery until the specified time.
  • (2) Note the point-in-time recovery SCN (alert log file)
  • (3) Source database in restricted mode.
  • (4) Stop the capture process and perform a data dictionary build as follows and note the SCN value returned.
   SET SERVEROUPUT ON
   DECLARE
   scn NUMBER;
   BEGIN
   DBMS_CAPTURE_ADM.BUILD (   first_scn => scn );
   DBMS_OUTPUT.PUT_LINE ('First SCN: ' || scn);
   END;
  • (5) [for each destination database] Determine the highest SCN for a transaction that was applied (after all transactions have been applied):
   select HWM_MESSAGE_NUMBER from V$STREAMS_APPLY_COORDINATOR where APPLY_NAME = 'STRMADMIN_APPLY';
   select APPLIED_MESSAGE_NUMBER from DBA_APPLY_PROGRESS where APPLY_NAME = 'STRMADMIN_APPLY';
  • May happen that, for each destination database, the highest SCNs are different. So we will need to combine scenario 4.1 and scenario 4.2.

Scenario 4.1. Point-in-time before latest data sent to Tier1 databases:

(Choose the greatest highest SCN) This means that the highest apply SCN (step 5) is greater than or equal to the point-in-time recovery SCN (step 2).
  • Could we identify the transactions which have been already applied at the destination sites???
  • Execute the transactions manually. Make sure you set a Streams tag in the session so the transaction will not be captured by the capture process.
  • Disable restricted session.
  • If highest SCNs are equal for all the destinations, then:
    • Drop capture process. Create new capture process using the same queue and the rule sets than before. Use the SCN returned by the data dictionary build (step 4).
    • Start capture processes.
  • If not, proceed to scenario 4.2.

Scenario 4.2. Point-in-time after latest data sent to Tier1 databases

  • Disable restricted session and ensure apply processes are running.
  • Set the maximum_scn capture parameter to the point-in-time recovery SCN (step 2) [or to the highest SCN if you were in scenario 4.1. before]
  • Set the start_scn of the capture process to the oldest SCN of the apply_process: select OLDEST_SCN_NUM from V$STREAMS_APPLY_READER; (choose the lowest one between each of the apply's oldest SCN).
    • If change is already applied at a destination database, then it is discarded by the apply process (inconvenience: network and computer resources use).
  • Ensure that the capture process writes information to the alert log and start capture process.
  • When the capture process stops, find the value of the LAST_ENQUEUE_MESSAGE_NUMBER in the alert log.
  • [destination database] Check all of the changes have been applied (this query should return a row): select DEQUEUED_MESSAGE_NUMBER from V$STREAMS_APPLY_READER where APPLY_NAME = 'STRMADMIN_APPLY' and DEQUEUED_MESSAGE_NUMBER = last_enqueue_message_number;
  • [destination database] Drop apply process. Create new apply process using the same queue and rule sets than before.
  • Drop capture process. Create new capture process using the same queue and the rule sets than before. Use the SCN returned by the data dictionary build (step 4).
  • Start apply and capture processes.

Scenario 5. Point-in-time recovery on the source and on the destinations:

  1. Point-in-time recovery on the source database using UNTIL TIME (we can use as test case the same as the scenario 4).
  2. Point-in-time recovery on the destination databases to the same point-in-time.
  3. Drop apply and capture processes and recreate apply and capture processes using the same queues and rule sets.

Topic attachments
I Attachment History Action Size Date Who Comment
Texttxt scenario1_test.txt r1 manage 9.9 K 2006-08-30 - 16:24 EvaDafonte  
Texttxt scenario4_test(1).txt r1 manage 6.5 K 2006-09-20 - 10:41 EvaDafonte  
Edit | Attach | Watch | Print version | History: r11 < r10 < r9 < r8 < r7 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r11 - 2006-12-12 - EvaDafonte
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PSSGroup All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback