Physical Standby

****Preparing the Primary Database for Physical Standby Database Creation ::

-- Enable Forced Logging

-- Create a password File

-- Configure a standby Redo Log

-- Set Primary database init parameter(DB_NAME,DB_UNIQUE_NAME,

LOG_ARCHIVE_CONFIG,LOG_ARCHIVE_DEST_1, LOG_ARCHIVE_DEST_2,

LOG_ARCHIVE_DEST_STATE_1=ENABLE,LOG_ARCHIVE_DEST_STATE_2=ENABLE,

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE,FAL_SERVER,FAL_CLIENT,

DB_FILE_NAME_CONVERT,LOG_FILE_NAME_CONVERT,STANDBY_FILE_MANAGEMENT=AUTO)

-- Enable Archiving

-- ALTER DATABASE ADD STANDBY LOGFILE THREAD 5

2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 10

2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

LOG_ARCHIVE_CONFIG parameter enables the database to send and receive redo; after a role transition, you may need to specify these settings again using the SEND, NOSEND, RECEIVE, or NORECEIVE keywords.

LOG_ARCHIVE_DEST_STATE_n Specify ENABLE to allow redo transport services to transmit redo data to the specified destination. thread (%t), sequence number (%s), and resetlogs ID (%r).

Creating Physical Standby::

- Create a Backup Copy of the Primary Database Datafiles( Primary DB)

- Create a Control File for the Standby Database ( Primary DB)

STARTUP MOUNT;

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';

SQL> ALTER DATABASE OPEN

alter - Prepare an Initialization Parameter File for the Standby Database( Primary DB)

1 -> Copy the primary database parameter file to the standby database.

CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;

2 -> Set initialization parameters on the physical standby database

(DB_NAME=(same as primarydb),DB_UNIQUE_NAME=boston, LOG_ARCHIVE_CONFIG,

DB_FILE_NAME_CONVERT,LOG_FILE_NAME_CONVERT, LOG_ARCHIVE_FORMAT

LOG_ARCHIVE_DEST_1,LOG_ARCHIVE_DEST_2 ,LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE,REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

STANDBY_FILE_MANAGEMENT=AUTO,FAL_SERVER=Chicago,FAL_CLIENT=boston)

- Copy Files from the Primary System to the Standby System ( Primary DB)

-> Backup datafiles created in Section

-> Standby control file created in Section

-> Initialization parameter file created in Section

- Set Up the Environment to Support the Standby Database( Standby DB)

Perform the following steps to create a Windows-based service, create a password file, set up the Oracle Net environment, and create a SPFILE

1 - oradim -NEW - SID boston -INTPWD password -STARTMODE man

2 - Create passwpord file

3 - Configure listeners for the primary and standby databases.

4 - Create Oracle Net service names.

5 - CREATE SPFILE FROM PFILE='initboston.ora';

- Start the Physical Standby Database( Standby DB)

Perform the following steps to start the physical standby database and Redo Apply

1- Start the physical standby database.

STARTUP MOUNT.

2- Start Redo Apply.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

FROM SESSION;

3 - Test archival operations to the physical standby SELECT database.

ALTER SYSTEM SWITCH LOGFILE;

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Post-Creation Steps :

Upgrade the data protection mode

Enable Flashback Database