At this stage we will create the Physical Standby Database and this task will take about 14 steps:

1- Backup the Datafiles in the Primary Database:

We need to copy the Datafiles from the Primary Database to create the Physical Standby Database, of course we can use RMAN or Physical Database, but I chose the easiest method Physical Backup, and here are the steps:

A- Close the database.

B- Using the operating system, copy all the Datafiles.

This way we have finished the backup issue.

2- Create the Control File for the Standby Database:

Run the Primary Database in Mount mode and then using SQL*Plus create the Standby Control File

ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘D:\BACKUP\;

 3- Create the variables file for the Standby Database:

Create the variables file for the Standby Database from the Primary Database.

CREATE PFILE='D:\BACKUP\initٍSTAN.ora' FROM SPFILE;

Then configure the new variables file to be compatible with the Standby Database. The variables file will look like this after modification:

DB_NAME=PRIM

DB_UNIQUE_NAME=STAN

SERVICE_NAMES=STAN

INSTANCE_NAME=STAN

LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'

CONTROL_FILES=' D:\oracle\product\10.1.0\oradata\PRIM\control1.ctl',  ‘D:\oracle\product\10.1.0\oradata\PRIM\control2.ctl’, ‘D:\oracle\product\10.1.0\oradata\PRIM\control3.ctl’

LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

LOG_ARCHIVE_DEST_1=

 'LOCATION=D:\oracle\product\10.1.0\flash_recovery_area\

  VALID_FOR= (ALL_LOGFILES, ALL_ROLES)

  DB_UNIQUE_NAME=STAN'

LOG_ARCHIVE_DEST_2=

 'SERVICE=PRIMNET

  VALID_FOR= (ONLINE_LOGFILES, PRIMARY_ROLE)

  DB_UNIQUE_NAME=PRIM'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER=PRIM

FAL_CLIENT=STAN

 Note that DB_NAME is the same for both Primary and Standby Database but they differ in DB_UNIQUE_NAME.

4- Create the required paths for Standby Database:

In this scenario we will follow exactly the same paths for both Primary and Standby Database, so create the paths for Data Files and Redo Log File in Standby Site as in Primary Site, but the paths may differ between Primary and Standby Database but you should then configure the variables (DB_FILE_NAME_CONVERT & LOG_FILE_NAME_CONVERT).

5- Move the backup files to the correct paths in Standby Database:

Including all Data Files, the variables file initstan.ora and also the Standby Controlfile after renaming it with the number of copies specified in the variables file.

6- Create the instance of the Standby Database:

This is done by creating the Service in Windows.

D:\ oradim -NEW -SID STAN -INTPWD password -STARTMODE manual

7- Create the Password File:

orapwd file= D:\oracle\product\10.1.0\Db_1\database\PWDSTAN.ora password=sys force=y entries=5

8- Configure listeners for both Primary and Standby Database:

The Listener for Primary Database will look like this:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER1)(PORT = 1521))

)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = D:\oracle\product\10.1.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = PRIM)

(ORACLE_HOME = D:\oracle\product\10.1.0)

(SID_NAME = PRIM)

)

(SID_DESC =

(GLOBAL_DBNAME = PRIM_DGMGRL)

(ORACLE_HOME = D:\oracle\product\10.1.0)

(SID_NAME = PRIM)

)

As for the Standby Database, the Listener will look like this:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER2)(PORT = 1521))

)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = D:\oracle\product\10.1.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = STAN)

(ORACLE_HOME = D:\oracle\product\10.1.0)

(SID_NAME = STAN)

)

(SID_DESC =

(GLOBAL_DBNAME = STAN_DGMGRL)

(ORACLE_HOME = D:\oracle\product\10.1.0)

(SID_NAME = STAN)

)

Then turn off and on the Listener.

D:\> lsnrctl stop
D:\>  lsnrctl start

9- Prepare the Tnsnames file for both the Primary and Standby Database:

For the Primary Site

PRIMNET =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = PRIM_DGMGRL)

    )

  )

STANNET =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER2)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = STAN_DGMGRL)

    )

  )

For Standby Site

STANNET =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER2)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = STAN_DGMGRL)

    )

  )

PRIMNET =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = PRIM_DGMGRL)

    )

  )

10- Activate Broken Connection Detection in the Standby Database:

This is done by setting the SQLNET.EXPIRE_TIME variable in the SQLNET.ORA file to take the value 2 (minute)

SQLNET.EXPIRE_TIME=2 

11- Run the Standby Database:

But before running the Standby Database, create the SPFILE from the PFILE file.

CREATE SPFILE FROM PFILE
STARTUP READ ONLY

Do not open the database for users. The Standby Database must remain in Mount or Read Only mode to receive Redo Data.

12- Create a new Temporary File for the Standby Database:

This step is important because the Standby Database may need the Temporary File for temporary operations such as sorting and others, especially when it is opened in OPEN READ ONLY mode, and users perform some queries. The role of the Standby Database may also change later.

A- Check the Tablespace that must contain Temporary Files

 'SELECT TABLESPACE_NAME FROM DBA_TABLESPACES  WHERE CONTENTS = 'TEMPORARY

b- Add a new Temp File to the Standby Database

ALTER TABLESPACE TEMP1 ADD TEMPFILE   ‘D:\oracle\product\10.1.0\oradata\orcl\temp01.dbf'  SIZE 40M REUSE;

13- Run Redo Apply:

We mentioned earlier that the Standby Database receives Redo Data from the Primary Database and then this data is applied in what is known as Redo Apply.

To run Redo Apply, execute this command on the Standby Database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

14- Verifying the proper performance of the Standby Database:

The process of transferring Redo Data to the Standby Database does not take place until after the Log Switch occurs. Originally, the Log Switch occurs when the Redo Log File is full, but the Log Switch can be done manually using the command

ALTER SYSTEM SWITCH LOGFILE;

To ensure that the Redo Data is received correctly in the Standby Database, do the following:

A- Check the current archive files in the Standby Database by querying

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

B- In the Primary Database, execute the command

ALTER SYSTEM ARCHIVE LOG CURRENT;

This command performs the Log Switch operation and archives the current Online Redo Log File Group.

C- To make sure that the New Redo Data has been archived in the Standby Database, perform the following query in the Standby Database

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

15- Verify the application of the archive that was sent to the Standby Database:

After we have confirmed that the archive has reached the Standby Database, we must verify its application (Redo Apply) in the Standby Database by querying

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;