Like dump database, quiesce database updates the dump sequence numbers if there have been unlogged writes. This prevents you from using an earlier database dump or external copy as an improper foundation for a dump sequence.
For example, in the warm standby method described below, archives are produced by dump database (D1), dump transaction (T1), quiesce database, dump transaction (T2), and dump transaction (T3):
Typically, in an environment with logged updates and no dump tran with truncate_only, you could load D1, T1, T2, and T3 in turn, bypassing any quiesce database hold. This approach is used in a warm standby situation, where succeeding database dumps on the primary server simplify media failure recovery scenarios. On the secondary, or standby server, which is used for decision-support systems, you may prefer continuous incremental applications of load transaction instead of interruptions from external copy operations.
However, if an unlogged operation occurs (for example, a select into, as happens above) after the dump transaction that produces T1, a subsequent dump transaction to archive is not allowed, and you must either create another dump of the database, or issue quiesce database for external copy and then make a new external copy of the database. Issuing either of these commands updates the dump sequence number and clears the mark that blocks the dump transaction to archive.
Whether or not you use the for external dump clause depends on how you want recovery to treat the quiescent database that would be marked as in quiesce.
quiesce database hold
If you issue quiesce database and do not use the for external dump clause, during the external copy operation that creates the secondary set of databases, the secondary server is not running, and recovery under -q does not see any copied database as “in quiesce.”
It recovers each server in the normal fashion during start-up recovery; it does not recover them as for load database as was previously described. Subsequently, any attempt to perform a load tran to any of these databases is disallowed with error 4306, "There was activity on database since last load ...", or with error 4305, "Specified file '%.*s' is out of sequence ..."
Whether or not there been unlogged activity in the primary database, the dump sequence number does not incremented by quiesce database hold, and the unlogged-writes bits are not cleared by quiesce database release.
Your query is blocked because it tried to write and database '%.*s' is in quiesce state. Your query will proceed after the DBA performs QUIESCE DATABASE RELEASE
The query is run once the database is no longer in a quiescent state.
quiesce database hold for external dump
When you issue quiesce database for external dump, the external copy of the database “remembers” that it was made during a quiescent interval, so that -q recovery can recover it, as happens for load database.
quiesce database release clears this information from the primary database. If unlogged writes have prevented dump tran to archive on the primary server, dump tran to archive is now enabled.
If unlogged writes have occurred since the previous dump database or quiesce database hold for external dump for any database in quiesce database’s list, the dump sequence number is updated by quiesce database hold for external dump, and the unlogged write information is cleared by quiesce database release. The updated sequence number causes load tran to fail if it is applied to a target other than the external copy created under the quiesce database that updated it. This resembles the behavior for dump database of a database with unlogged writes status.