Use SQL*Plus to Convert to Physical Stanby

Summary

The snapshot standby can be restored to its normal function as a physical standby through through SQL*Plus using the procedure described in 10.7.3 Converting a Snapshot Standby Database into a Physical Standby Database.

References

Oracle Manuals

Procedure

Setup

One (1) system is involved:

  1. BOTANY

Ensure Correct State

On BOTANY, use the following SQL*Plus command to find out what the current mode and role is for the database instance:

select open_mode, database_role from v$database;

The expected output is:

OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE SNAPSHOT STANDBY

Shut Database Instance Down

On BOTANY, use the following SQL*Plus command to shutdown the database instance:

shutdown immediate

The expected output is:

Database closed. Database dismounted. ORACLE instance shut down.

Put Instance Into MOUNT Mode

On BOTANY, use the following SQL*Plus command to mount the database instance:

startup mount

The expected output is:

ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 2923920 bytes Variable Size 973079152 bytes Database Buffers 268435456 bytes Redo Buffers 13852672 bytes Database mounted.

Check Instance Mode and Role

On BOTANY, use the following SQL*Plus command to find out what the current mode and role is for the database instance:

select open_mode, database_role from v$database;

The expected output is:

OPEN_MODE DATABASE_ROLE -------------------- ---------------- MOUNTED SNAPSHOT STANDBY

Convert to Physical Standby

On BOTANY, use the following SQL*Plus command to find out what the current mode and role is for the database instance:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

The expected output is:

Database altered.

Check Instance Mode and Role

On BOTANY, use the following SQL*Plus command to find out what the current mode and role is for the database instance:

select open_mode, database_role from v$database;

The expected output is:

OPEN_MODE DATABASE_ROLE -------------------- ---------------- MOUNTED PHYSICAL STANDBY