Step by Step Physical Standby database creation

Environment:

OS: Red Hat Enterprise Linux Server release 6.10

Oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Hostname: primary.localdomain

                    stdby.localdomain

Oracle binary and Oracle database accdb has been installed on host primary where as we have installed Oracle binaries only on the standby host stdby. While installing Oracle on standby host we have selected install software only.

Primary

Verify database is on archive log mode or not. Convert primary database to archivelog mode.

SELECT log_mode FROM v$database;

Shutdown immediate

startup mount

alter database archivelog;

alter database open;

Check force logging has been enabled or not. Set database into force logging.

select name, force_logging from v$database;

alter database force logging;

Verify db_name and db_unique_name parameter. Both value should be same for primary database but  they will be different for standby database.

show parameter db_name

show parameter db_unique_name

Configure Standby related Oracle database parameters.

alter system set log_archive_config='DG_CONFIG=(accdb,accstdb)';

alter system set log_archive_dest_2='SERVICE=accstdb NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=accstdb';

alter system set log_archive_dest_state_2=enable;

alter system set fal_server='accstdb';

alter system set fal_client='accdb';

alter system set standby_file_management=auto;

Check additional parameters

show parameter passwordfile

show parameter log_archive_format

SID_LIST_LISTENER =

(SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = accdb)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = accdb)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = accdb_dgmgrl)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = accdb)

    )

  )

Configure listener and tnsnames

ACCDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = accdb)

    )

  )

ACCSTDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = stdby.localdomain)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = accdb)

    )

  )

Reload listener and check listener status

lsnrctl reload

lsnrctl status

Create pfile to oracle home from current spfile.

Create pfile from spfile;

Now ship recently created pfile and password file from primary to standby server.

cd

scp pfile oracle@stdby:

cd $ORACLE_HOME/dbs

scp orapwaccdb oracle@stdby:

Ship listener and tnsnames files to standy server.

cd $ORACLE_HOME/network/admin

scp *.ora stdby:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

Add standby redolog files to primary database

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo01.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo02.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo03.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo04.log' SIZE 52428800;

Standby

Create required directories on standby server

mkdir -p /u01/app/oracle/admin/accdb/adump

mkdir -p /u01/app/oracle/oradata/accdb

mkdir -p /u01/app/oracle/admin/DB11G/accdb

mkdir -p /u01/app/oracle/flash_recovery_area

mkdir -p /u01/app/oracle/flash_recovery_area/accdb

Open listener.ora file with the text editor and modify the high lighted sections as host and GLOBAL_DBNAME.

vi listener.ora

Reload listener and check listener status

lsnrctl reload

lsnrctl status

Open pfile with text editor and modify highlighted sections.

cd

vi pfile

Copy Password file.

cp orapwaccdb /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

login to standby server and create spfile from pfile. Once spfile is created startup database on nomount mode.

sqlplus / as sysdba

create spfile from pfile='/home/oracle/pfile';

startup nomount

Primary Database

Tnsping to accdb and accstdb tnsnames

tnsping accdb

tnsping accstdb

Connect to standby server as user sys. Just to make sure we can connect to standby server.

sqlplus sys@accstdb as sysdba

If you are able to connect to the standby server then connect rman target as primary and auxiliary as standby server.

rman target / auxiliary sys@accstdb

Execute duplicate command to create standby database from active database

duplicate target database for standby from active database nofilenamecheck;

Add logfile to standby database;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

ALTER DATABASE ADD LOGFILE '/u01/app/oracle/oradata/accdb/redo01.log' SIZE 52428800;

ALTER DATABASE ADD LOGFILE '/u01/app/oracle/oradata/accdb/redo02.log' SIZE 52428800;

ALTER DATABASE ADD LOGFILE '/u01/app/oracle/oradata/accdb/redo03.log' SIZE 52428800;

Add redo log and standby redo log files

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo01.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo02.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo03.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo04.log' SIZE 52428800;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Check database open_mode and its role

select name, open_mode, database_role from v$database;

Alter database to recovery mode.

alter database recover managed standby database disconnect;

Verify logfile switch.

alter system switch logfile;

We can noticed RFS receives archive log and apply

Active Standby database with Realtime apply

Cancel recovery on managed standby and open database on read only mode.

alter database recover managed standby database cancel;

alter database open read only;

Start recovery on managed standby 

alter database recover managed standby database using current logfile;

Create table on primary.

create table abc(sn number);

insert into abc values(1);

/

commit;

Verify table and rows on standby server

select * from abc;