Steps to configure Dataguard Physical Standby Setup with cold backup

Steps involved for configure the oracle database data guard on windows system. we are taking primary and secondary server.

Step by Step configure Oracle Data guard physical standby database

Steps by Step configure Primary Database server

1. Login as Oracle user in Primary Database Server:

SQLPLUS sys as sysdba

2. Determine if FORCE LOGGING is enabled.
If it is not enabled, enable FORCE LOGGING mode. This statement may take some time to complete, because it waits for all unlogged direct write I/O to finish. You use the following commands:

SELECT force_logging FROM v$database;

-- Enable the force logging if disabled.
ALTER DATABASE FORCE LOGGING;

3. Create pfile from spfile.

Create pfile='location' from spfile;

4. Open parameter file INITprim.ORA in notepad and add below mentioned parameter. Add below line in newly created pfile

*.DB_UNIQUE_NAME='prim'
*.FAL_CLIENT='prim'
*.FAL_SERVER='prim_dr'
*.LOG_ARCHIVE_CONFIG='dg_config=(prim,prim_dr)'
*.LOG_ARCHIVE_DEST_1='location=D:\oracle\product\archive valid_for=(all_logfiles,all_roles) db_unique_name=prim'
*.log_archive_dest_2='service=prim_dr lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=prim_dr'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='prim_%t_%s_%r.arc'
*.remote_login_passwordfile='EXCLUSIVE'
*.Standby_archive_dest='D:\oracle\product\archive'
*.STANDBY_FILE_MANAGEMENT='AUTO'

5. Now startup database using newly created pfile.

$ cd $ORACLE_HOME/bin
$ ./sqlplus '/ as sysdba'

SQL> startup pfile='D:\oracle\db_1\database\INITprim.ORA';
 
SQL> create spfile from pfile='D:\oracle\db_1\database\ INITprim.ORA';

6. Check the parameter whether all set properly.

SQL> show parameter log_archive_dest_1;
SQL> show parameter log_archive_dest_2;
SQL> show parameter fal_server; 

7. Shutdown database and startup again using spfile.

SQL> shutdown immediate;
SQL > Startup

8. Create the control file for standby server on primary database.

SQL> Alter database create standby controlfile as
D:\oracle\product\oradata\PRIM\CONTROLSTB01.CTL

9. Modify the Listener.ora file on Primary server.

# listener.ora Network Configuration File: D:\oracle\product\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.0.14)(PORT = 1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = prim)
(ORACLE_HOME = D:\oracle\product\db_1)
)
(SID_DESC =
(SID_NAME = prim_dr)
(ORACLE_HOME = D:\oracle\product\db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.0.14)(PORT = 1521))
)
)

TRACE_LEVEL_LISTENER = ADMIN

10. Make entry for standby database in the Tnsnames.ora file on Primary server.

# tnsnames.ora Network Configuration File: D:\oracle\product\db_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.0.14)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)

PRIM_dr =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.0.15)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim_dr)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Step On Standby server

1. Copy the file ‘D:\oracle\product\db_1\database\ INITprim.ORA’ from Primary Server to Secondary server using FTP or SCP utility.

2. Open parameter file INITprim.ORA in notepad and add below mentioned parameter. Change the entries as below

*.DB_UNIQUE_NAME='prim_dr'
*.FAL_CLIENT='prim_dr'
*.FAL_SERVER='prim'
*.LOG_ARCHIVE_CONFIG='dg_config=(prim_dr,prim)'
*.LOG_ARCHIVE_DEST_1='location=D:\oracle\product\archive valid_for=(all_logfiles,all_roles) db_unique_name=prim_dr'
*.log_archive_dest_2='service=prim_dr lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=prim'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='prim_%t_%s_%r.arc'
*.remote_login_passwordfile='EXCLUSIVE'
*.Standby_archive_dest='D:\oracle\product\archive'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.control_files='D:\oracle\product\oradata\PRIM\CONTROLSTB01.CTL'

3. Oracle Binaries are already installed on this server.

4. Create Oracle service using below command on windows system.

C:\> oradim -NEW -SID prim

NOTE: Service must be same as production database.

5. Create password file using below command.

C:\> orapwd file= D:\oracle\product\db_1\database\pwdprim.ora password=’sys$123’ entries=10

NOTE: Password must be same as production database.

6. Copy Datafile with cold backup.

Copy all files from Primary server to standby on the same directory location
i.e.
\\10.157.0.14\D:\oracle\product\10.2.0\oradata\PRIM
To
\\10.157.0.15\D:\oracle\product\10.2.0\oradata\PRIM

7. Now set environment variables.

Login as oracle user
C:\>set ORACLE_HOME= D:\oracle\product\10.2.0\db_1
C:\>set ORACLE_SID= prim_dr
C:\>cd $ORACLE_HOME/bin
C:\>sqlplus '/ as sysdba'

8. Startup in nomount state using pfile.

SQL> startup nomount pfile = 'D:\oracle\product\db_1\database\ INITprim.ORA'

9. Create spfile from pfile.

create spfile from pfile = ‘D:\oracle\product\db_1\database\ INITprim.ORA’

10. Startup and Shutdown in nomount mode.

SQL> shutdown immediate;
SQL> startup nomount;
SQL> exit

This will create the standby database.

11. Listener file on standby database.

# listener.ora Network Configuration File: D:\oracle\product\db_1\NETWORK\ADMIN\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = prim_dr)
(SID_NAME = PRIM)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.0.15)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

12. Tnsnames.ora file on standby database.

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.0.14)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)

PRIM_DR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.0.15)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim_dr)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

13. For redo apply on Standy by database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP
---------- ---
302 YES
303 YES
2 rows selected.