Performing Point-in-Time Recovery on the Source in a Single-Source Environment

Nel documento Oracle® Streams Replication Administrator’s Guide 10g (pagine 197-200)

Performing Point-in-Time Recovery in a Multiple-Source Environment

Performing Point-in-Time Recovery on a Destination Database

Performing Point-in-Time Recovery on the Source in a Single-Source Environment

A single-source Streams replication environment is one in which there is only one source database for shared data. If database point-in-time recovery is required at the source database in a single-source Streams environment, and any capture processes that capture changes generated at a source database are running, then you must stop these capture processes before you perform the recovery operation. Both local and downstream capture process that capture changes generated at the source database must be stopped. Typically, database administrators reset the log sequence number of a database during point-in-time recovery. The ALTER DATABASE OPEN RESETLOGS statement is an example of a statement that resets the log sequence number.

The instructions in this section assume that the single-source replication environment has the following characteristics:

Only one capture process named strm01_capture, which can be a local or downstream capture process

Only one destination database with the global name dest.net

Only one apply process named strm01_apply at the destination database If point-in-time recovery must be performed on the source database, then you can follow these instructions to recover as many transactions as possible at the source database by using transactions applied at the destination database. These instructions

See Also: Oracle Database Backup and Recovery Advanced User's Guide for more information about point-in-time recovery

Performing Database Point-in-Time Recovery in a Streams Environment

assume that you can identify the transactions applied at the destination database after the source point-in-time SCN and execute these transactions at the source database.

Complete the following steps to perform point-in-time recovery on the source database in a single-source Streams replication environment:

1. Perform point-in-time recovery on the source database if you have not already done so. Note the point-in-time recovery SCN because it is needed in subsequent steps.

2. Ensure that the source database is in restricted mode.

3. Stop the capture process using the STOP_CAPTURE procedure in the DBMS_

CAPTURE_ADM package.

4. At the source database, perform a data dictionary build:

SET SERVEROUTPUT ON DECLARE

scn NUMBER;

BEGIN

DBMS_CAPTURE_ADM.BUILD(

first_scn => scn);

DBMS_OUTPUT.PUT_LINE('First SCN Value = ' || scn);

END;

/

Note the SCN value returned because it is needed in Step 13.

5. At the destination database, wait until all of the transactions from the source database in the apply process queue have been applied. The apply processes should become idle when these transactions have been applied. You can query the STATE column in both the V$STREAMS_APPLY_READER and V$STREAMS_

APPLY_SERVER. The state should be IDLE for the apply process in both views before you continue.

6. Perform a query at the destination database to determine the highest SCN for a transaction that was applied.

If the apply process is running, then perform the following query:

SELECT HWM_MESSAGE_NUMBER FROM V$STREAMS_APPLY_COORDINATOR WHERE APPLY_NAME = 'STRM01_APPLY';

If the apply process is disabled, then perform the following query:

SELECT APPLIED_MESSAGE_NUMBER FROM DBA_APPLY_PROGRESS WHERE APPLY_NAME = 'STRM01_APPLY';

Note the highest apply SCN returned by the query because it is needed in subsequent steps.

7. If the highest apply SCN obtained in Step 6 is less than the point-in-time recovery SCN noted in Step 1, then proceed to step 8. Otherwise, if the highest apply SCN obtained in Step 6 is greater than or equal to the point-in-time recovery SCN noted in Step 1, then the apply process has applied some transactions from the source

Note: Oracle recommends that you set the apply process parameter COMMIT_SERIALIZATION to FULL when performing point-in-time recovery in a single-source Streams replication environment.

Performing Database Point-in-Time Recovery in a Streams Environment

database after point-in-time recovery SCN. In this case complete the following steps:

a. Manually execute transactions applied after the point-in-time SCN at the source database. When you execute these transactions at the source database, make sure you set a Streams tag in the session so that the transactions will not be captured by the capture process. If no such Streams session tag is set, then these changes can be cycled back to the destination database. See "Managing Streams Tags for the Current Session" on page 9-26 for instructions.

b. Disable the restricted session at the source database.

8. If you completed the actions in Step 7, then proceed to Step 12. Otherwise, if the highest apply SCN obtained in Step 6 is less than the point-in-time recovery SCN noted in Step 1, then the apply process has not applied any transactions from the source database after point-in-time recovery SCN. In this case, complete the following steps:

a. Disable the restricted session at the source database.

b. Ensure that the apply process is running at the destination database.

c. Set the maximum_scn capture process parameter of the original capture process to the point-in-time recovery SCN using the SET_PARAMETER procedure in the DBMS_CAPTURE_ADM package.

d. Set the start SCN of the original capture process to the oldest SCN of the apply process. You can determine the oldest SCN of a running apply process by querying the OLDEST_SCN_NUM column in the V$STREAMS_APPLY_READER dynamic performance view at the destination database. To set the start SCN of the capture process, specify the start_scn parameter when you run the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

e. Ensure that the capture process writes information to the alert log by running the following procedure:

BEGIN

DBMS_CAPTURE_ADM.SET_PARAMETER(

capture_name => 'strm01_capture', parameter => 'write_alert_log', value => 'Y');

END;

/

f. Start the original capture process using the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

g. Ensure that the original capture process has captured all changes up to the maximum_scn setting by querying the CAPTURED_SCN column in the DBA_

CAPTURE data dictionary view. When the value returned by the query is equal to or greater than the maximum_scn value, the capture process should stop automatically. When the capture process is stopped, proceed to the next step.

h. Find the value of the LAST_ENQUEUE_MESSAGE_NUMBER in the alert log.

Note this value because it is needed in subsequent steps.

Performing Database Point-in-Time Recovery in a Streams Environment

i. At the destination database, wait until all the changes are applied. You can monitor the applied changes for the apply process strm01_apply by running the following queries at the destination database:

SELECT DEQUEUED_MESSAGE_NUMBER FROM V$STREAMS_APPLY_READER

WHERE APPLY_NAME = 'STRM01_APPLY' AND

DEQUEUED_MESSAGE_NUMBER = last_enqueue_message_number;

Substitute the LAST_ENQUEUE_MESSAGE_NUMBER found in the alert log in Step h for last_enqueue_message_number on the last line of the query. When this query returns a row, all of the changes from the capture database have been applied at the destination database.

Also, ensure that the state of the apply process reader server and each apply server is IDLE. For example, run the following queries for an apply process named strm01_apply:

SELECT STATE FROM V$STREAMS_APPLY_READER WHERE APPLY_NAME = 'STRM01_APPLY';

SELECT STATE FROM V$STREAMS_APPLY_SERVER WHERE APPLY_NAME = 'STRM01_APPLY';

When both of these queries return IDLE, move on to the next step.

9. At the destination database, drop the apply process using the DROP_APPLY procedure in the DBMS_APPLY_ADM package.

10. At the destination database, create a new apply process. The new apply process should use the same queue and rule sets used by the original apply process.

11. At the destination database, start the new apply process using the START_APPLY procedure in the DBMS_APPLY_ADM package.

12. Drop the original capture process using the DROP_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

13. Create a new capture process using the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package to replace the capture process you dropped in Step 12. Specify the SCN returned by the data dictionary build in Step 4 for both the first_scn and start_scn parameters. The new capture process should use the same queue and rule sets as the original capture process.

14. Start the new capture process using the START_CAPTURE procedure in the DBMS_

CAPTURE_ADM package.

Nel documento Oracle® Streams Replication Administrator’s Guide 10g (pagine 197-200)