Notes of Oracle 11.2 Dataguard setup

1. Change primary database to force logging mode
alter database force logging;

2. Create standby control
alter database create standby controlfile as '/tmp/control.ctl';

3. Primary database parameter file
-bash-4.1$ more inittestdb.ora
testdb.__db_cache_size=1929379840
testdb.__java_pool_size=16777216
testdb.__large_pool_size=16777216
testdb.__oracle_base='/apps/oracle'#ORACLE_BASE set from environment
testdb.__pga_aggregate_target=1677721600
testdb.__sga_target=2516582400
testdb.__shared_io_pool_size=0
testdb.__shared_pool_size=520093696
testdb.__streams_pool_size=0
*.audit_file_dest='/apps/oracle/admin/testdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data0/oradata/testdb/control01.ctl','/data1/oradata/testdb/control02.ctl','/data2/oradata/testdb/control03.ctl'
*.db_block_size=8192
*.db_domain='fidelity.co.jp'
*.db_name='testdb'
*.db_unique_name=testdb
*.db_recovery_file_dest='/archive2/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/apps/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.log_archive_config='dg_config=(testdb,testdbsb)'
*.log_archive_dest_1='location=/archive1/oradata/testdb'
*.log_archive_dest_2='service=testdbsb async valid_for=(online_logfile,primary_role) db_unique_name=testdbsb compression=enable'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=4183818240
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.fal_server=testdbsb
*.fal_client=testdb
*.db_file_name_convert='/testdbsb/','/testdb/'
*.log_file_name_convert='/testdbsb/','/testdb/'
*.standby_file_management=auto

4. testdbsb parameter file
-bash-4.1$ more inittestdbsb.ora
testdbsb.__db_cache_size=1929379840
testdbsb.__java_pool_size=16777216
testdbsb.__large_pool_size=16777216
testdbsb.__oracle_base='/apps/oracle'#ORACLE_BASE set from environment
testdbsb.__pga_aggregate_target=1677721600
testdbsb.__sga_target=2516582400
testdbsb.__shared_io_pool_size=0
testdbsb.__shared_pool_size=520093696
testdbsb.__streams_pool_size=0
*.audit_file_dest='/apps/oracle/admin/testdbsb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data0/oradata/testdbsb/control01.ctl','/data1/oradata/testdbsb/control02.ctl','/data2/oradata/testdbsb/control03.ctl'
*.db_block_size=8192
*.db_domain='fidelity.co.jp'
*.db_name='testdb'
*.db_unique_name=testdbsb
*.db_recovery_file_dest='/archive2/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/apps/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbsbXDB)'
*.log_archive_config='dg_config=(testdb,testdbsb)'
*.log_archive_dest_1='location=/archive1/oradata/testdbsb'
*.log_archive_dest_2='service=testdb async valid_for=(online_logfile,primary_role) db_unique_name=testdb compression=enable'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=4183818240
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
# for failover
*.fal_server=testdb
*.fal_client=testdbsb
*.db_file_name_convert='/testdb/','/testdbsb/'
*.log_file_name_convert='/testdb/','/testdbsb/'
*.standby_file_management=auto

5. Copy datafile,control,logfile to standby database server

6. Setup listener and tnsname in primary

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=testcidb01vl.cn.fid-intl.com)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=testdb_DGMGRL.fidelity.co.jp)
      (ORACLE_HOME=/apps/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=testdb))
    (SID_DESC=
      (GLOBAL_DBNAME=testdb.fidelity.co.jp)
      (ORACLE_HOME=/apps/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=testdb))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/apps/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc))
    )

ADR_BASE_LISTENER = /apps/oracle

-bash-4.1$ more tnsnames.ora
testdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = testcidb01vl.cn.fid-intl.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb.fidelity.co.jp)
    )
  )

testdbSB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bcpcidb01vl.hk.fid-intl.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdbsb.fidelity.co.jp)
    )
  )

7. Setup listener and tnsname in standby

-bash-4.1$ more listener.ora
LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=bcpcidb01vl.hk.fid-intl.com)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=testdbsb_DGMGRL.fidelity.co.jp)
      (ORACLE_HOME=/apps/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=testdbsb))
    (SID_DESC=
      (GLOBAL_DBNAME=testdbsb.fidelity.co.jp)
      (ORACLE_HOME=/apps/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=testdbsb))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/apps/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc))
  )

ADR_BASE_LISTENER = /apps/oracle

-bash-4.1$
-bash-4.1$ more tnsnames.ora
testdbSB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bcpcidb01vl.hk.fid-intl.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdbsb.fidelity.co.jp)
    )
  )

testdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = testcidb01vl.cn.fid-intl.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb.fidelity.co.jp)
    )
)

-bash-4.1$

8.Create standby log file in production database testdb

Configure the primary database to receive redo data, by adding the standby logfiles to the primary


alter database add standby logfile group 6 '/data0/oradata/testdb/sby_redo01.log' size 50M;
alter database add standby logfile group 7 '/data1/oradata/testdb/sby_redo02.log' size 50M;
alter database add standby logfile group 8 '/data2/oradata/testdb/sby_redo03.log' size 50M;
alter database add standby logfile group 9 '/data3/oradata/testdb/sby_redo04.log' size 50M;
alter database add standby logfile group 10 '/data0/oradata/testdb/sby_redo05.log' size 50M;

9. Create folder in standby server

mkdir /apps/oracle/admin/testdbsb
mkdir /apps/oracle/admin/testdbsb/adump
mkdir /data0/oradata/testdbsb/
mkdir /data1/oradata/testdbsb/
mkdir /data2/oradata/testdbsb/
mkdir /data3/oradata/testdbsb/
mkdir /archive1/oradata/testdbsb/

10. Put standby database in recovery mode

SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 1175525576 bytes
Fixed Size 452808 bytes
Variable Size 335544320 bytes
Database Buffers 838860800 bytes
Redo Buffers 667648 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.