Oracle DBA Lesson 32D — Choosing a Point-in-Time Recovery

Point-in-time recovery deliberately returns the database to an earlier consistent state. Choose the boundary from the incident and the business decision. Removing unwanted work also discards legitimate changes made after that point.

Oracle DBA Lesson 32D — Choosing a Point-in-Time Recovery

Concept and worked decision

Database point-in-time recovery replaces the CDB's data files with suitable backups and applies recovery changes to an earlier consistent state. With SET UNTIL SCN 300, recovery stops before SCN 300. The command's upper limit is noninclusive. See UNTIL clause.

Authored committed marker	Commit SCN	Expected at target 300
Good marker	200	Present
Unwanted marker	300	Absent
Later valid marker	350	Absent — intentional business loss

These numbers illustrate the boundary and are not actual lab evidence. The CDB target applies to all its PDBs. Have the business owner approve later legitimate changes that will be lost, and compare a separately planned narrower PDB or table recovery, or an applicable Flashback procedure, when scope permits. See database point-in-time recovery.

Place SET UNTIL before both RESTORE and RECOVER so backup selection and redo application share the target. A completed incomplete recovery leads to OPEN RESETLOGS and a new incarnation. Record that history and protect a fresh recovery baseline. Older backups can still be useful. Preserve the prior incarnation and the required media under the retention and runbook decision. See SET.

Practice and operating conditions

Use only the disconnected disposable CDB approved in 32A, with no source or shared data, control, FRA, or redo paths, no source catalog registration, and no production service endpoints. Use the recorded database identity and current incarnation, the approved Oracle home and release update, the current control file and parameters, ARCHIVELOG, data-file backups earlier than the target, and required redo covering every applicable file through the limit. Provide applicable encryption keys and configured channel and media access. Confirm paths before the first restore write. A change to a previous incarnation, or a missing control file, requires its own reviewed recovery runbook.

Root lifecycle operations require authorized SYSDBA. RMAN requires a common SYSBACKUP or SYSDBA target operator. The local examples below assume the lab owner verified OSDBA and OSBACKUPDBA membership, the environment, and the isolated target. They are unexecuted examples. Start logging before mutations, and preserve the actual identity, parameters, file maps, target evidence, the before and after incarnation list, and application expectations outside the recoverable VM.

1. Prepare a controlled marker workload: one committed good marker before the baseline backup, a verified stop boundary, an unwanted committed marker, then a distinct legitimate later marker. Preserve backups and spanning redo. The instructor records and corroborates the exact target from transaction and recovery evidence. A current-SCN snapshot is not automatically the commit SCN of a row. Ensure the selected limit preserves the good work and excludes the unwanted commit. Replace the placeholder below with that approved SCN from the same database and incarnation. Never run the teaching number 300 against a lab merely because it appears in the video.

2. Record the DBID, database, and service identity, the file, control, redo, and FRA destinations, and the applicable keys. Verify each path is confined to the lab. If a source path, an unexpected identity, an unsupported boundary, missing media, or unapproved loss remains, stop before restore. Keep the required PDBs, application checks, and approved downtime in the plan.

3. In SQL*Plus, connect with the verified local SYSDBA route and mount the isolated CDB:

   SHUTDOWN IMMEDIATE
   STARTUP MOUNT

4. Start RMAN with a separate job log, then connect to the same isolated root through the lab's configured local OSBACKUPDBA route:

   rman log=lab32d_recovery_01.log
   CONNECT TARGET "/ AS SYSBACKUP";
   LIST INCARNATION;
   RUN {
     SET UNTIL SCN <VERIFIED_STOP_SCN>;
     RESTORE DATABASE;
     RECOVER DATABASE;
   }

   SET UNTIL selects the target for both operations within this block. RESTORE writes the chosen data files. Verify that the log names the intended pieces and files. RECOVER must complete to the approved target without unresolved errors. Preserve any error and investigate its documented cause before proceeding. Changing the target, widening the scope, or improvising an opening command requires a new recovery decision.

5. Only after successful incomplete recovery, open the isolated CDB through SQL*Plus. See ALTER DATABASE.

   ALTER DATABASE OPEN RESETLOGS;
   SELECT con_id, name, open_mode FROM v$pdbs ORDER BY con_id;

   Open only the required named PDBs when their observed modes require it. For the course tenant:

   ALTER PLUGGABLE DATABASE LABPDB OPEN;

6. Record the new history in RMAN, then leave the client:

   LIST INCARNATION;
   EXIT

   Compare the DBID and name, the CURRENT and PARENT incarnation records, and the reset SCN and time with the saved pre-run history. See LIST. Reconnect the course account through the verified isolated LABPDB service and inspect the actual service and container identity and the markers:

   SELECT SYS_CONTEXT('USERENV','CON_NAME') AS container_name,
          SYS_CONTEXT('USERENV','SERVICE_NAME') AS service_name FROM dual;
   SELECT marker_id, marker_text FROM recovery_marker ORDER BY marker_id;

   Pass when the good marker is present, the unwanted marker and the designated later-valid markers are absent, legitimate lost changes are documented and business-approved, the required PDBs and services work, the logs prove the selected recovery path, and the operator can explain the CDB-wide impact. Record the measured recovery and application verification time as useful operating evidence. Preserve actual results rather than filling expectations in as execution.

7. Take a fresh baseline backup under the reviewed backup procedure. Retain the required earlier backups, incarnation records, and recovery logs. Decommission only the isolated VM under its owner's runbook after acceptance. Do not reuse this changed-incarnation copy for 32E's current-control-file complete-recovery drill.

Recap

Agree the earlier boundary and the legitimate data loss, set the target before restore and recover, open with RESETLOGS after successful incomplete recovery, and verify the business result and the new incarnation.

Quiz

1. With SET UNTIL SCN 300, how is SCN 300 treated?

It is the only included SCN The limit is noninclusive. Recovery stops before it All commits after it are retained It is ignored during backup selection

2. Which markers are expected in the worked example: good at 200, unwanted at 300, later valid at 350?

All three Only 300 and 350 Only 350 Only the good marker at 200

3. Where should SET UNTIL be placed in the shown RUN block?

Before both RESTORE and RECOVER After RECOVER finishes Only after OPEN RESETLOGS Inside the marker SELECT

4. Should RESETLOGS be tried whenever ordinary OPEN fails?

Yes. It fixes every file error Yes. It avoids recovery planning No. Diagnose the error and complete the required recovery path No. Oracle never supports RESETLOGS

5. What scope and business consequence need approval for this CDB PITR?

Only the selected marker table changes All PDBs share the CDB target, and later legitimate changes may be lost Only the listener is affected The new incarnation proves that no data was lost

Names, paths, marker values, and expected results are teaching examples. Run the practice in the designated Oracle Database 19c lab, confirm the exact release update, and record what you actually observe against the stated success criteria.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro