Automated Standby Database as read-only

To start up a read-only database using Fast-Start Fault Recovery in Oracle, you can follow these steps:

1. Stop the database instance.
   

   SHUTDOWN IMMEDIATE;
   

2. Start up the database instance in open read-only mode.
   

   STARTUP OPEN READ ONLY;
   

   
   If the database instance has any uncommitted transactions, Oracle will start parallel rollback of those transactions before opening the database in read-only mode.
3. Verify that the database is open in read-only mode.
   

   SELECT DATABASE_READ_MODE FROM V$DATABASE;
   

   
   The output of this query should show the value `READ_ONLY`.

Once the database is open in read-only mode, users can connect to the database and perform read-only operations, such as SELECT and SHOW. However, they will not be able to perform any write operations, such as INSERT, UPDATE, or DELETE. If you need to change the database back to read-write mode, you need to shut down the database instance and then start it up again in normal mode.
Note: If you are using Oracle Data Guard, you can also use the physical standby database as a read-only database. To do this, you need to start up the physical standby database in open read-only mode.
Example:

STARTUP OPEN READ ONLY PHYSICAL STANDBY;

Once the physical standby database is open in read-only mode, users can connect to the database and perform read-only operations.

Benefits of using a read-only Database

• Read-only databases can be used to improve the performance of read-only workloads.
• Read-only databases can be used to improve the security of a database by preventing unauthorized users from making changes to the data.
• Read-only databases can be used to create a backup of a database without impacting the performance of the production database.

A feature of the automated standby database^[1] is the ability to use a standby database as a read-only database. In the past, a standby database could not be opened or used without subsequently re-cloning the production database. Thus, resources allocated to a standby database were idle and could not be used for anything other than backing up the production database. Within Oracle, the standby database can be taken out of the recovery mode and opened for read-only query processing. This can be used to temporarily offload queries, and for reporting, even while archive logs are being copied from the primary database site. When the query processing is complete, the standby database can return to standby mode without re-cloning the production database. Other backup copies of a database can also be opened in read-only mode and used for querying. This provides more productive resource usage while maintaining high database availability.

The read-only mode allows users to query an open database, thereby eliminating the potential for online data modifications. If you maintain your standby database primarily for disaster prevention, you should not rely too heavily on it as a source of information. If a disaster occurs, you will be forced to activate the database quickly, ceasing all user activity. If you need both disaster prevention and a standby available for queries, you can maintain multiple standby databases, some read-only and some within managed recovery mode. Although you will need to resynchronize the read-only database, the recovery mode databases give you protection against disaster.
To open the standby database in read-only mode when the database is shut down:

1. Startup the database and mount it in standby mode
2. Open the database in read-only mode

If any changes are made to the primary database, such as adding or altering tablespaces, data files, redo log files, or control files, these changes must be manually transferred to the standby database. The next lesson is the module wrap-up.

Oracle provides a reliable and supported mechanism for implementing a standby database system to facilitate quick disaster recovery. This mechanism is called Automated Standby Database. Up to four standby systems can be maintained in a constant state of media recovery through the automatic shipping and application of log files archived at the primary site. In the event of a primary system failure, one of the standby systems can be activated, providing immediate system availability. Oracle provides commands and internal verifications for operations involved in the creation and maintenance of the standby systems, improving the reliability of the disaster recovery scheme.
A standby database uses the archived log information from the primary database, so it is ready to perform recovery and go online at any time. When the primary database archives its redo logs, the logs must be transferred to the remote site and applied to the standby database. The standby database is therefore always one or two logs behind the primary database in time and transaction history. Automated Standby Database protects your data from extended outages such as power failures, or from physical disasters such as fire, floods, or earthquakes. Because the standby database is designed for disaster recovery, it ideally resides in a separate physical location from the primary database. You can open the standby database read only. This allows you to use the database for reporting. When you open a standby database read only, redo logs are placed in a queue and are not applied. As soon as the database is returned to standby mode, the queued logs and newly arriving logs are applied.

[1]automated standby database:A standby database is a database replica created from a backup of a primary database.