OPEN means the instance finished recovery and will take sessions. It is not a handoff. Acceptance is four proofs, each with evidence, a pass line, and a named owner. If a proof was not observed, that pillar stays PENDING, and so does the handoff.
Oracle DBA Lesson 20B — Define How a New Database Will Be Accepted
Two clocks, and neither one is OPEN
Recovery point and recovery time are different promises. RPO is how much data you may lose, measured backward from the failure. An RPO of 15 minutes means a restore must reach a committed state no older than that. RTO is how long the business waits for a useful service. An RTO of 2 hours includes finding the failure, restoring files, applying redo, opening, and a real application call. It is not the elapsed time of the backup job.
ARCHIVELOG is what makes a 15-minute RPO possible. In NOARCHIVELOG you can only return to the last consistent backup. Archivelog mode does not, by itself, keep that promise. Redo still sitting in the online logs is not in an archived copy. If those disks die, that redo dies with them. ARCHIVE_LAG_TARGET forces a switch after the number of seconds you set. The default is 0, which disables it. A target of 900 seconds bounds how long redo waits in the online log. It does not copy that archive off the volume that just failed.
-- Gap since the newest local archive. That gap is still only
-- in the online redo, until some other destination has it.
-- 1440 converts days to minutes.
SELECT MAX(next_time) AS newest_archived,
ROUND((SYSDATE - MAX(next_time)) * 1440) AS minutes_behind
FROM v$archived_log
WHERE status = 'A'
AND standby_dest = 'NO';A full backup from last night plus ARCHIVELOG meets a 15-minute RPO only when the archived logs through the last 15 minutes exist on storage that survives the failure you actually planned for. Archives and backup pieces that live only on the data volume are one failure, not a recovery copy.
A green backup did not time the RTO
V$RMAN_BACKUP_JOB_DETAILS.STATUS = 'COMPLETED' means that backup job finished. ELAPSED_SECONDS on that row is how long the backup took. It is not a restore, and it is not the RTO. The pieces can be complete and still be unreadable next month, still sitting on the disks you will lose, or still missing the archived logs the RPO needs.
RESTORE DATABASE VALIDATE is the next check, and it is still not the drill. RMAN chooses the backup sets, copies, and archived logs it would use, then reads the blocks. Oracle's reference is exact: validation is the same as a restore except that RMAN does not write output files. A clean run means the pieces can be read. It does not create datafiles, so it cannot fail on a full destination filesystem. It does not apply redo, open the database, or accept an application session. A 25-minute validate does not prove a 2-hour RTO. The clock you care about has not started.
-- Reads every block in the pieces it would use.
-- Writes no datafiles. Does not RECOVER, OPEN, or time the RTO.
RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-1' VALIDATE;RECOVER DATABASE TEST is a trial recovery. Redo is applied in memory and then rolled back. Nothing is written to the datafiles. Use it to find a corrupt archive before the real drill. Do not enter its elapsed time in the RTO column.
The proof that matches the promise is smaller than the slogan and harder than the job status: a timed restore onto storage that is not production, recovery to a point inside the RPO, an open database, and a fresh application connection. Isolated means a different host or different disks, with SET NEWNAME or DUPLICATE, so a mistake cannot replace the files you just accepted. DUPLICATE without FOR STANDBY assigns a new DBID. Keep that copy off the production recovery catalog. OPEN RESETLOGS on the production DBID, while connected to that catalog, records a new incarnation the real database did not have.
Identity and config, written down
The first pillar is whether this is the database the plan named. OPEN does not answer that. Record the DBID before the first drill. If every control file is gone and you have no catalog, RMAN asks for it.
-- Write these into the acceptance sheet. DBID is not recoverable
-- from a guess, and a later COMPATIBLE change does not roll back.
SELECT dbid, name, db_unique_name, log_mode, open_mode,
database_role, force_logging, flashback_on, cdb,
platform_name
FROM v$database;SELECT instance_name, status, logins, database_status
FROM v$instance;
-- Root can be OPEN while the application PDB is not.
SELECT name, open_mode, restricted
FROM v$pdbs;LOGINS = RESTRICTED still reports the instance OPEN. Accounts that lack RESTRICTED SESSION get ORA-01035. A SYSDBA session does not. V$PDBS.OPEN_MODE = MOUNTED means the application container is not available, whatever V$DATABASE.OPEN_MODE says about the root.
Character set and national character set are acceptance items because they are not a parameter you tune after go-live. So is COMPATIBLE. FORCE_LOGGING = NO matters to the RPO: a NOLOGGING load is not in the redo stream. Media recovery marks those blocks corrupt. If the business promise includes those tables, the pass line is FORCE_LOGGING = YES, or a written ban on nologging loads that the owner has agreed to. Flashback logging is a separate feature. FLASHBACK_ON is not ARCHIVELOG, and it is not required for the 15-minute RPO.
SELECT property_name, property_value
FROM database_properties
WHERE property_name IN (
'NLS_CHARACTERSET',
'NLS_NCHAR_CHARACTERSET',
'DEFAULT_TBS_TYPE')
ORDER BY property_name;Pass: every value matches the creation sheet the application signed. Owner: the DBA who built the database. Evidence: the query output, dated, not a memory of the DBCA screens.
Recovery is a drill you watched
Confirm CONFIGURE CONTROLFILE AUTOBACKUP ON in SHOW ALL. A completed backup without an autobackup still leaves you searching for a control file when the copies on the data volume are gone. Confirm the archived-log deletion policy before anyone schedules DELETE INPUT. BACKUP ARCHIVELOG ALL DELETE INPUT removes the local archive after the backup piece is written. If that piece is on the same disks as the database, you deleted the copy the RPO needed.
The pass line for this example is specific. On storage that is not the production volume, restore and recover to a chosen failure time, land within 15 minutes of that time, open, and reach a useful application call inside 2 hours. The clock starts when you declare the drill, not when RMAN prints "Starting restore". Detection and the application check are part of a 2-hour useful service. Record the row while you watch it. An empty row is not a pass.
-- Fill this in during the drill. Blank fields stay PENDING.
-- Do not restore onto the production datafiles.
host:
declared_start:
rman_restore_start:
open_time:
app_smoke_time:
elapsed_to_useful:
recovered_until:
rpo_limit: 15 min
rto_limit: 2 h
open_mode:
app_host:
service:
smoke_result:
watched_by:Owner: the DBA who runs the drill, and the backup owner who confirms the pieces came from the copy that would survive. A validate log is supporting evidence. It does not fill this sheet.
Application access is a connection you did not make
sqlplus / as sysdba on the server uses bequeath. It never touches the listener, the service name, the password, the wallet, or the firewall from the application hosts. It will succeed while the application cannot connect at all.
The pass line is a new session from an application host, using the service the application uses, as the account the application uses, running one call the application actually runs. lsnrctl status must show that service READY, not merely UNKNOWN from a static listener.ora entry. In a multitenant database the PDB has to be open and the service started. The default PDB service and a user-defined service are not interchangeable if the connection string names one of them.
Owner: the application owner runs the call. The DBA owns the service and the account. If the DBA ran it from the database server, Application Access was not observed.
Operations, or the proof expires
A backup someone ran by hand on create day is not an operation. The pass line is one scheduled run that already finished without a person starting it, plus a named person who is paged when the next one fails. The schedule lives in the cron, the scheduler, or the job system you will still have in six months. The alert route names a human. "The DBA team" is not a name.
Write down where the alert log is (V$DIAG_INFO), where the autobackup goes, and which catalog connect string is production. Archive deletion has to be slower than the RPO copy. A policy of NONE, followed by a cleanup script, can drop archives a restore still needs.
Four pillars, nothing implied:
- Identity and config. Evidence: DBID, name, unique name, log mode, character sets, PDB open mode, force logging, compatible. Pass: matches the signed sheet. Owner: the creating DBA.
- Recovery. Evidence: autobackup on, archives surviving off the data volume inside the RPO, and the drill sheet above. Pass: within 15 minutes of the chosen point, and a useful service inside 2 hours, on other storage. Owner: DBA and backup owner.
- Application access. Evidence: the app-host session, the service, the account, the smoke call. Pass: that call succeeds. Owner: application owner and DBA.
- Operations. Evidence: one unattended backup already completed, a named on-call, a deletion policy that cannot eat the RPO. Pass: those three exist. Owner: operations.
If any line was not observed, the handoff stays PENDING. Do not promote it because the instance is OPEN and the last backup job is green.
Gotchas
OPENwithLOGINS = RESTRICTEDstill looks open. The application getsORA-01035.SYSDBAdoes not.- The root can be open while
V$PDBSshows the application PDBMOUNTED. Check the PDB the service points at. ARCHIVELOGis not a 15-minute RPO. Redo not yet archived, and archives stored only on the failed volume, are both lost.ARCHIVE_LAG_TARGETonly forces the switch.NOLOGGINGloads are not in the redo. After recovery those blocks are corrupt.FORCE_LOGGINGis the database-level answer when the RPO covers those tables.COMPLETEDinV$RMAN_BACKUP_JOB_DETAILSis the backup.ELAPSED_SECONDSthere is not the RTO.RESTORE DATABASE VALIDATEreads the pieces and writes nothing.RECOVER DATABASE TESTapplies redo in memory only. Neither one opens a database or connects an application.DELETE INPUTand a deletion policy ofNONEcan remove the only archived copy once the backup piece is on the same disks as the datafiles.sqlplus / as sysdbaon the server does not prove the listener, the service, the account, or the path from the application hosts.OPEN RESETLOGSon a clone that still has the production DBID, connected to the production catalog, records an incarnation production did not create.DUPLICATEwithoutFOR STANDBYgives the copy a new DBID. Keep the drill off that catalog.- A pillar with a blank evidence line stays
PENDING. The handoff does not default to accepted.
Quick quiz
1. V$DATABASE shows OPEN and ARCHIVELOG. The last RMAN backup job is COMPLETED. Nobody has restored it. What is the handoff?
2. RESTORE DATABASE VALIDATE finishes with no errors in 25 minutes. The RTO is 2 hours. What did you prove?
3. RPO is 15 minutes. The newest archived log is 3 hours old. Archives, backups, and online redo are only on the data volume. That volume is lost. What is gone?
4. You connect with / as sysdba on the database server and query V$INSTANCE. Nobody has connected from an application host. Which pillar is still open?
OPEN means the instance finished recovery and will take sessions. It is not a handoff. Acceptance is four proofs, each with evidence, a pass line, and a named owner. If a proof was not observed, that pillar stays PENDING, and so does the handoff.
Oracle DBA Lesson 20B — Define How a New Database Will Be Accepted
Two clocks, and neither one is OPEN
Recovery point and recovery time are different promises. RPO is how much data you may lose, measured backward from the failure. An RPO of 15 minutes means a restore must reach a committed state no older than that. RTO is how long the business waits for a useful service. An RTO of 2 hours includes finding the failure, restoring files, applying redo, opening, and a real application call. It is not the elapsed time of the backup job.
ARCHIVELOG is what makes a 15-minute RPO possible. In NOARCHIVELOG you can only return to the last consistent backup. Archivelog mode does not, by itself, keep that promise. Redo still sitting in the online logs is not in an archived copy. If those disks die, that redo dies with them. ARCHIVE_LAG_TARGET forces a switch after the number of seconds you set. The default is 0, which disables it. A target of 900 seconds bounds how long redo waits in the online log. It does not copy that archive off the volume that just failed.
-- Gap since the newest local archive. That gap is still only
-- in the online redo, until some other destination has it.
-- 1440 converts days to minutes.
SELECT MAX(next_time) AS newest_archived,
ROUND((SYSDATE - MAX(next_time)) * 1440) AS minutes_behind
FROM v$archived_log
WHERE status = 'A'
AND standby_dest = 'NO';A full backup from last night plus ARCHIVELOG meets a 15-minute RPO only when the archived logs through the last 15 minutes exist on storage that survives the failure you actually planned for. Archives and backup pieces that live only on the data volume are one failure, not a recovery copy.
A green backup did not time the RTO
V$RMAN_BACKUP_JOB_DETAILS.STATUS = 'COMPLETED' means that backup job finished. ELAPSED_SECONDS on that row is how long the backup took. It is not a restore, and it is not the RTO. The pieces can be complete and still be unreadable next month, still sitting on the disks you will lose, or still missing the archived logs the RPO needs.
RESTORE DATABASE VALIDATE is the next check, and it is still not the drill. RMAN chooses the backup sets, copies, and archived logs it would use, then reads the blocks. Oracle's reference is exact: validation is the same as a restore except that RMAN does not write output files. A clean run means the pieces can be read. It does not create datafiles, so it cannot fail on a full destination filesystem. It does not apply redo, open the database, or accept an application session. A 25-minute validate does not prove a 2-hour RTO. The clock you care about has not started.
-- Reads every block in the pieces it would use.
-- Writes no datafiles. Does not RECOVER, OPEN, or time the RTO.
RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-1' VALIDATE;RECOVER DATABASE TEST is a trial recovery. Redo is applied in memory and then rolled back. Nothing is written to the datafiles. Use it to find a corrupt archive before the real drill. Do not enter its elapsed time in the RTO column.
The proof that matches the promise is smaller than the slogan and harder than the job status: a timed restore onto storage that is not production, recovery to a point inside the RPO, an open database, and a fresh application connection. Isolated means a different host or different disks, with SET NEWNAME or DUPLICATE, so a mistake cannot replace the files you just accepted. DUPLICATE without FOR STANDBY assigns a new DBID. Keep that copy off the production recovery catalog. OPEN RESETLOGS on the production DBID, while connected to that catalog, records a new incarnation the real database did not have.
Identity and config, written down
The first pillar is whether this is the database the plan named. OPEN does not answer that. Record the DBID before the first drill. If every control file is gone and you have no catalog, RMAN asks for it.
-- Write these into the acceptance sheet. DBID is not recoverable
-- from a guess, and a later COMPATIBLE change does not roll back.
SELECT dbid, name, db_unique_name, log_mode, open_mode,
database_role, force_logging, flashback_on, cdb,
platform_name
FROM v$database;SELECT instance_name, status, logins, database_status
FROM v$instance;
-- Root can be OPEN while the application PDB is not.
SELECT name, open_mode, restricted
FROM v$pdbs;LOGINS = RESTRICTED still reports the instance OPEN. Accounts that lack RESTRICTED SESSION get ORA-01035. A SYSDBA session does not. V$PDBS.OPEN_MODE = MOUNTED means the application container is not available, whatever V$DATABASE.OPEN_MODE says about the root.
Character set and national character set are acceptance items because they are not a parameter you tune after go-live. So is COMPATIBLE. FORCE_LOGGING = NO matters to the RPO: a NOLOGGING load is not in the redo stream. Media recovery marks those blocks corrupt. If the business promise includes those tables, the pass line is FORCE_LOGGING = YES, or a written ban on nologging loads that the owner has agreed to. Flashback logging is a separate feature. FLASHBACK_ON is not ARCHIVELOG, and it is not required for the 15-minute RPO.
SELECT property_name, property_value
FROM database_properties
WHERE property_name IN (
'NLS_CHARACTERSET',
'NLS_NCHAR_CHARACTERSET',
'DEFAULT_TBS_TYPE')
ORDER BY property_name;Pass: every value matches the creation sheet the application signed. Owner: the DBA who built the database. Evidence: the query output, dated, not a memory of the DBCA screens.
Recovery is a drill you watched
Confirm CONFIGURE CONTROLFILE AUTOBACKUP ON in SHOW ALL. A completed backup without an autobackup still leaves you searching for a control file when the copies on the data volume are gone. Confirm the archived-log deletion policy before anyone schedules DELETE INPUT. BACKUP ARCHIVELOG ALL DELETE INPUT removes the local archive after the backup piece is written. If that piece is on the same disks as the database, you deleted the copy the RPO needed.
The pass line for this example is specific. On storage that is not the production volume, restore and recover to a chosen failure time, land within 15 minutes of that time, open, and reach a useful application call inside 2 hours. The clock starts when you declare the drill, not when RMAN prints "Starting restore". Detection and the application check are part of a 2-hour useful service. Record the row while you watch it. An empty row is not a pass.
-- Fill this in during the drill. Blank fields stay PENDING.
-- Do not restore onto the production datafiles.
host:
declared_start:
rman_restore_start:
open_time:
app_smoke_time:
elapsed_to_useful:
recovered_until:
rpo_limit: 15 min
rto_limit: 2 h
open_mode:
app_host:
service:
smoke_result:
watched_by:Owner: the DBA who runs the drill, and the backup owner who confirms the pieces came from the copy that would survive. A validate log is supporting evidence. It does not fill this sheet.
Application access is a connection you did not make
sqlplus / as sysdba on the server uses bequeath. It never touches the listener, the service name, the password, the wallet, or the firewall from the application hosts. It will succeed while the application cannot connect at all.
The pass line is a new session from an application host, using the service the application uses, as the account the application uses, running one call the application actually runs. lsnrctl status must show that service READY, not merely UNKNOWN from a static listener.ora entry. In a multitenant database the PDB has to be open and the service started. The default PDB service and a user-defined service are not interchangeable if the connection string names one of them.
Owner: the application owner runs the call. The DBA owns the service and the account. If the DBA ran it from the database server, Application Access was not observed.
Operations, or the proof expires
A backup someone ran by hand on create day is not an operation. The pass line is one scheduled run that already finished without a person starting it, plus a named person who is paged when the next one fails. The schedule lives in the cron, the scheduler, or the job system you will still have in six months. The alert route names a human. "The DBA team" is not a name.
Write down where the alert log is (V$DIAG_INFO), where the autobackup goes, and which catalog connect string is production. Archive deletion has to be slower than the RPO copy. A policy of NONE, followed by a cleanup script, can drop archives a restore still needs.
Four pillars, nothing implied:
- Identity and config. Evidence: DBID, name, unique name, log mode, character sets, PDB open mode, force logging, compatible. Pass: matches the signed sheet. Owner: the creating DBA.
- Recovery. Evidence: autobackup on, archives surviving off the data volume inside the RPO, and the drill sheet above. Pass: within 15 minutes of the chosen point, and a useful service inside 2 hours, on other storage. Owner: DBA and backup owner.
- Application access. Evidence: the app-host session, the service, the account, the smoke call. Pass: that call succeeds. Owner: application owner and DBA.
- Operations. Evidence: one unattended backup already completed, a named on-call, a deletion policy that cannot eat the RPO. Pass: those three exist. Owner: operations.
If any line was not observed, the handoff stays PENDING. Do not promote it because the instance is OPEN and the last backup job is green.
Gotchas
OPENwithLOGINS = RESTRICTEDstill looks open. The application getsORA-01035.SYSDBAdoes not.- The root can be open while
V$PDBSshows the application PDBMOUNTED. Check the PDB the service points at. ARCHIVELOGis not a 15-minute RPO. Redo not yet archived, and archives stored only on the failed volume, are both lost.ARCHIVE_LAG_TARGETonly forces the switch.NOLOGGINGloads are not in the redo. After recovery those blocks are corrupt.FORCE_LOGGINGis the database-level answer when the RPO covers those tables.COMPLETEDinV$RMAN_BACKUP_JOB_DETAILSis the backup.ELAPSED_SECONDSthere is not the RTO.RESTORE DATABASE VALIDATEreads the pieces and writes nothing.RECOVER DATABASE TESTapplies redo in memory only. Neither one opens a database or connects an application.DELETE INPUTand a deletion policy ofNONEcan remove the only archived copy once the backup piece is on the same disks as the datafiles.sqlplus / as sysdbaon the server does not prove the listener, the service, the account, or the path from the application hosts.OPEN RESETLOGSon a clone that still has the production DBID, connected to the production catalog, records an incarnation production did not create.DUPLICATEwithoutFOR STANDBYgives the copy a new DBID. Keep the drill off that catalog.- A pillar with a blank evidence line stays
PENDING. The handoff does not default to accepted.
No comments:
Post a Comment