---

Creating Oracle database 12c Standby database manually step by step.

Your primary database is named as orcl and create a standby database name as stdby.

Here we are using two terminals ,terminal-1 is used for the primary database and terminal-2 is used for the standby database

 

TERMINAL-1:

1.       Connect as sysdba and Enable FORCE LOGGING mode of database.

$ sqlplus / as sysdba

SQL> alter database force logging;

Check the force logging using following command

SQL> SELECT force_logging FROM v$database;

FORCE_LOGGING

---------------------------------------

YES

2.       Configure the standby redo logs on the primary database, check if OMF is not enabled, enable it first.

SQL> alter system set db_create_file_dest='/disk1/oradata/orcl';

To check the OMF

SQL> show parameter db_create_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest string /disk1/oradata/orcl

Configure the FRA if it is not set

SQL> alter system set db_recovery_file_dest_size=5g;

SQL> alter system set db_recovery_file_dest='/disk3/oradata/orcl/fra';

Check the db_recovery_file_dest using following query

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /disk3/oradata/orcl/fra

 

SQL> alter database add standby logfile;

SQL> alter database add standby logfile;

Check the logfile information

SQL> select group#, member from v$logfile;

3.       Set the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2 initialization parameters on the primary database.

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

 

SQL> alter system set log_archive_dest_2='SERVICE=tns_stdby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby';

SQL> alter system set log_archive_config='dg_config=(prod,stdby)';

Check the above parameters using following query

SQL> SHOW PARAMETER LOG_ARCHIVE_DEST

SQL> SHOW PARAMETER LOG_ARCHIVE_CONFIG

4.       Configure the ARCHIVELOG mode, if its not enabled fallow the below steps to enable it.

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 563

Current log sequence 564

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

SQL> EXIT

5.       Invoke RMAN and make a whole database backup.

$ rman target /

RMAN> backup database plus archivelog;

RMAN> EXIT

 

TERMINAL-2:

6.       Configure LISTENER on Standby database by logging into standby server.

$ export ORACLE_SID=stdby

$ cd $HOME/network

$ vi listener.ora

-------------------------------------------------------------------

LIS_stdby =

    (DESCRIPTION_LIST =

        (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.48)(PORT = 1531))

        )

    )

SID_LIST_LIS_stdby =

    (SID_LIST =

        (SID_DESC =

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

            (SID_NAME = stdby)

        )

    )

-------------------------------------------------------------------

7.  Check the listener configuration & start listener service.

$ cd

$ vi .bash_profile

export TNS_ADMIN=$HOME/network

$ . .bash_profile

$ lsnrctl start lis_stdby

 

 

 

TERMINAL-1:

8.       On your primary database system, create an tns alias for your physical standby database.

$ cd $HOME/network

$ vi tnsnames.ora

Add the below new entries mentioning host & port details of Standby database.

-------------------------------------------------------------------

tns_stdby =

    (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.48)(PORT = 1531))

        (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = stdby)

        )

    )

-------------------------------------------------------------------

9.       Check the tns entries configured properly.

$ cd

$ vi .bash_profile

export TNS_ADMIN=$HOME/network

$ . .bash_profile

Now from primary server use tnsping command and check the listener availability.

$ tnsping tns_stdby

10.   Copy your primary database password file to the standby database system.

$ cd $ORACLE_HOME/dbs

$ cp orapworcl orapwstdby

11.   On the primary database host, create PFILE and copy it to standby server as initstdby.ora in $ORACLE_HOME/dbs location.

$ sqlplus / as sysdba

SQL> create pfile='$HOME/initprod.ora' from spfile;

Copy the pfile into the oracle’s default location $ORACLE_HOME/dbs

$ cp $HOME/initorcl.ora $ORACLE_HOME/dbs/initstdby.ora

12.   Edit initstdby.ora file on standby server.

$ cd $ORACLE_HOME/dbs

$ vi initstdby.ora

-> remove all *.__ parameter entries

-> wherever "orcl", replace it with "stdby" for control_files, db_create_file_dest, db_recovery_file_dest, diagnostic_dest

-> change "stdby" to "orcl" for parameter log_archive_dest_2.

-> add new parameters, from below and save the file.

db_name=orcl

log_archive_max_processes='5'

standby_file_management='AUTO'

fal_server='orcl'

fal_client='stdby'

db_file_name_convert='/disk1/oradata/orcl','/disk1/oradata/stdby',

’/disk2/oradata/orcl','/disk2/oradata/stdby'

 

log_file_name_convert='/disk1/oradata/orcl','/disk1/oradata/stdby',

’/disk2/oradata/orcl','/disk2/oradata/stdby'

TERMINAL-2:

13.   On the standby host, create required directories in /disk1, /disk2 & /disk3

$ mkdir /disk1/oradata/stdby

$ mkdir /disk2/oradata/stdby

$ mkdir /disk3/oradata/stdby/fra

14.   Start the instance with your parameter file just updated.

$ sqlplus / as sysdba

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initstdby.ora';

SQL> EXIT

 

TERMINAL-1:

15.   On the primary database host, Invoke RMAN and connect as SYSDBA to the target database & auxiliary database.

$ rman target sys/oracle auxiliary sys/oracle@tns_stdby

Execute script to create standby database

RMAN> run {

duplicate target database for standby from active database

spfile parameter_value_convert 'orcl','stdby' set db_unique_name='stdby';

}

RMAN> EXIT

 

Verifying That the Physical Standby Database Is Performing Correctly

 

TERMINAL-2:

16.   On the standby database, identify the existing archived redo log files.

SQL> SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;

 

TERMINAL-1:

17.   Perform a log switch on the primary database.

$ sqlplus / as sysdba

SQL> alter system switch logfile;

SQL> alter system switch logfile;

 

TERMINAL-2:

 

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

18.   On physical standby database, Start Redo Apply.

SQL> alter database recover managed standby database using current logfile disconnect;

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

---