This tutorial is for configuring
physical standby database.


The dataguard type which we are configuring
here is "Maximum Performance Mode".

Primary Machine IP: 192.168.1.160

Database name: orcl



Secondary Machine IP: 192.168.1.162

Database name:standby



On Primary Machine

1) Enable force logging on primary machine.


SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES



2) Create a password file if its not made.

[oracle@primary ~]$ orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/
orapworcl password=sys

3) Create standby redo logs

The rule is to create one more Standby redo log group than the

total number of online redo log groups.


SQL> col members format a50
SQL> select group#, thread#, bytes, members from v$log;

GROUP# THREAD# BYTES MEMBERS
---------- ---------- ---------- ----------
1 1 52428800 ##########
2 1 52428800 ##########
3 1 52428800 ##########

# I already have three redo groups, So i will create a total of four
# standby groups.

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

GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log


SQL> alter database add standby logfile group 4
2 '/u01/app/oracle/oradata/orcl/stby04.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 5
2 '/u01/app/oracle/oradata/orcl/stby05.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 6
2 '/u01/app/oracle/oradata/orcl/stby06.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 7
2 '/u01/app/oracle/oradata/orcl/stby07.log' size 50m;

Database altered.


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

GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log
4 STANDBY /u01/app/oracle/oradata/orcl/stby04.log
5 STANDBY /u01/app/oracle/oradata/orcl/stby05.log
6 STANDBY /u01/app/oracle/oradata/orcl/stby06.log
7 STANDBY /u01/app/oracle/oradata/orcl/stby07.log


7 rows selected.


4) Edit parameter file



SQL> create pfile='/u01/app/initorcl.ora' from spfile;

File created.



Parameters

orcl.__db_cache_size=188743680
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=83886080
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl',
'/u01/app/oracle/oradata/orcl/control02.ctl',
'/u01/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

db_unique_name='orcl'
log_archive_config='DG_CONFIG=(orcl,standby)'
log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,
all_roles) db_unique_name=orcl'
log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,
primary_role) db_unique_name=standby'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=arch%t_%s_%r.arc
log_archive_max_processes=4


#standby role parameters

fal_server=standby
fal_client=orcl
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/standby/',
'/u01/app/oracle/oradata/orcl/'
log_file_name_convert='/u01/app/oracle/oradata/standby/',
'/u01/app/oracle/oradata/orcl/'

# Shutdown the database and create spfile
# from the edited pfile.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount pfile='/u01/app/initorcl.ora';
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes


SQL> create spfile from pfile='/u01/app/initorcl.ora';

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.



# Also put the database in archivelog mode.
# I have already mentioned the archive destination
# in the parameter file.

SQL> startup mount
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3



Primary machine Backup.

Before going any further make identical directory structures
for backups on both the primary as well as secondary machines.




primary: mkdir -p /u01/app/oracle/backup
secondary: mkdir -p /u01/app/oracle/backup

Now we will take the backup of the orcl database on the primary machine.


[oracle@primary ~]$ export ORACLE_SID=orcl
[oracle@primary ~]$ rman target=/

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 6 20:22:38 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1299658972)

RMAN> backup device type disk format'/u01/app/oracle/backup/%U'
database plus archivelog;


RMAN> backup device type disk format '/u01/app/oracle/backup/%U'
current controlfile for standby;

# Backup the control file aditionally for standby server.


Prepare the Standby Server or Secondary Machine.

Make appropriate directory structures.

[oracle@secondary ~]$ mkdir -p /u01/app/oracle/admin/standby/adump
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/admin/standby/bdump
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/oradata/standby/
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/admin/standby/cdump
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/admin/standby/udump
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/arch



Copy the pfile as well as the backups from the primary machine to standby.

scp -r /u01/app/initorcl.ora 192.168.1.162:/u01/app/oracle/product/
10.2.0/db_1/dbs/initstandby.ora


scp -r /u01/app/oracle/backup/* 192.168.1.162:/u01/app/oracle/backup/


Edit the pfile for standby server.


Standby Parameters

standby.__db_cache_size=188743680
standby.__java_pool_size=4194304
standby.__large_pool_size=4194304
standby.__shared_pool_size=83886080
standby.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/standby/adump'
*.background_dump_dest='/u01/app/oracle/admin/standby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/standby/control01.ctl',
'/u01/app/oracle/oradata/standby/control02.ctl',
'/u01/app/oracle/oradata/standby/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/standby/udump'

db_unique_name='standby'
log_archive_config='DG_CONFIG=(orcl,standby)'
log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,
all_roles) db_unique_name=standby'
log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles
,primary_role) db_unique_name=orcl'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=arch%t_%s_%r.arc
log_archive_max_processes=4

#standby role parameters

fal_server=orcl
fal_client=standby
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/orcl/',
'/u01/app/oracle/oradata/standby/'
log_file_name_convert='/u01/app/oracle/oradata/orcl/',
'/u01/app/oracle/oradata/standby/'





Configure listeners on both the machines.

Primary listener

Code:
# listener.ora Network Configuration File: /u01/app/
oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.160)(PORT = 1521))
  )
Tnsnames.ora Common parameters on both the Machines.

Code:
# tnsnames.ora Network Configuration File: /u01/app/oracle/
product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.162)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = standby)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.160)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Secondary or Standby listener.

Code:
# listener.ora Network Configuration File: /u01/app/oracle
/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = standby)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = standby)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.162)(PORT = 1521))
  )
Start the listeners on both servers.


Do tnsping on both to check their status.

tnsping orcl
tnsping standby


create password file on standby

[oracle@secondary ~]$ orapwd file=/u01/app/oracle/product/10.2.0/db_1/
dbs/orapwstandby password=sys


Start the standby database to nomount stage.

[oracle@secondary ~]$ export ORACLE_SID=standby
[oracle@secondary ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 6 21:24:06 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initstandby.ora';

File created.


SQL> startup nomount
exit

Connect to both the orcl and standby database using RMAN and start the
standby database creation process.

[oracle@secondary ~]$ export ORACLE_SID=standby
[oracle@secondary ~]$ rman target=sys/sys@orcl nocatalog auxiliary=/

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 6 21:28:12 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1299658972)
using target database control file instead of recovery catalog
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby;



After the orcl database is duplicated to standby
exit from rman, and configure the standby database
to receive redo logs from orcl.


[oracle@secondary ~]$ export ORACLE_SID=standby
[oracle@secondary ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 6 21:35:04 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database recover managed standby database disconnect
from session;

Database altered.


On Primary

Restart the instance.
SQL>shutdown immediate
SQL>startup

SQL> alter system switch logfile;

System altered.

SQL> alter system archive log current;

System altered.

SQL> archive log list;
Code:
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/arch
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence           12
On Secondary



SQL> archive log list;
Code:
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/arch
Oldest online log sequence     10
Next log sequence to archive   0
Current log sequence           12
SQL> select sequence#, applied from v$archived_log order by sequence#;

Note:Restart database on primary and reload listeners for ora 12154
errors in alertlog file.



To shutdown the standby database execute the following procedure.


find out if standby database is performing managed recovery.
If the MRP0 or MRP process exists then it is performing
managed recovery.

SQL> select process, status from v$managed_standby;

cancel managed recovery operations.

SQL> alter database recover managed standby database cancel;

shutdown the standby database

SQL> shutdown immediate;



To startup the standby database execute the folowing commands.

SQL> startup nomount;

SQL> alter database mount standby database;

SQL> alter database recover managed standby database
disconnect from session;