At this stage we will create the Physical Standby Database and this task will take several 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

Windows
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘D:\BACKUP\’;
Linux
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/Backup/’;

3- Create the parameter file for the Standby Database:

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

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

Then configure the new parameter file to be compatible with the Standby Database. The parameter 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 the DB_NAME is the same for both Primary and Standby Databases but they differ in DB_UNIQUE_NAME.

4- Create the required paths for the Standby Database:

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

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

Including all the Data Files, the parameter 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 for 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 stop and start the Listener.

lsnrctl stop
lsnrctl start

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

For 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 to 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 should 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;

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#;