Physical Standby Database with 11GR1

OS: Windows 2003

Oracle: 11GR1

Primary : DBORA

Standby:  DBORADG

Tns Primary: DBORA

Standby Tns: DBORADG

Primary and standby on same host.

1. Preparing the primary database for standby db.

1.1 Enable force logging

1.2 copy password file from primary to standby site

1.3 Setup init file 

1.4 Enable archive log ( if database is not in archive log mode)

2.Steps for physical standby database

2.1 Create backup

2.2 Create controlfile for standby database

2.3 create init file from primary database

2.3.1 modify init paramter file as per physical standby db

2.3.2 create spfile from modified pfile for physical standby db

2.4 set up environment to support physical standby

2.4.1 create standby db service via ORADIM utility

2.4.2 copy password file from primary to standby site

2.4.3 configure listener via LSNRCTL utlity ( stop and start)

2.4.4 configure TNSNAMES.ora file in $ORACLE_HOME/network/admin

2.4.5 create spfile file from modified pfile 

3. Start and verify physical standby db

3.1 start the physical standby db

3.2 verify the physical standby database is performing properly

4. Primary database changes that require manual intervention at a physical standby

4.1 Add a datafile or create a tablespace

4.2 Drop or delete a tablespace or datafile

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> --enable force logging

SQL> alter database force logging;

Database altered.

SQL> --copy password file from primary site to standby site

SQL> --setup init file

SQL> alter system set db_unique_name='DBORA' scope=spfile;

System altered.

SQL> alter system set log_archive_config='DG_CONFIG=(dbora,dboradg)' scope=spfil

e;

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=e:\arch\dbora

  2  valid_for=(all_logfiles,all_roles)

  3  db_unique_name=dbora' scope=spfile;

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=dboradg ASYNC

  2  valid_for=(online_logfiles,primary_role)

  3  db_unique_name=dboradg' scope=spfile;

System altered.

SQL> alter system set log_archive_dest_state_1=enable scope=spfile;

System altered.

SQL> alter system set log_archive_dest_state_2=enable scope=spfile;

System altered.

SQL> --setup init parameter for standby db in primary site

SQL> alter system set fal_server=dboradg scope=spfile;

System altered.

SQL> alter system set fal_client=dbora scope=spfile;

System altered.

SQL> alter system set db_file_name_convert='E:\app\Administrator\oradata\dboradg

','E:\app\Administrator\oradata\DBORA' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='E:\app\Administrator\oradata\dborad

g','E:\app\Administrator\oradata\DBORA' scope=spfile;

System altered.

SQL> alter system set standby_file_management=auto scope=spfile;

System altered.

SQL> --enable archive log mode

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1334380 bytes

Variable Size             167773076 bytes

Database Buffers          360710144 bytes

Redo Buffers                5844992 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            e:\arch\dbora

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

SQL> alter database open;

Database altered.

SQL> --create backup for physical standby ( i am using COLDBACKUP)

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> --start db in mount mode to create controlfile to standby

SQL> startup mount

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1334380 bytes

Variable Size             167773076 bytes

Database Buffers          360710144 bytes

Redo Buffers                5844992 bytes

Database mounted.

SQL> alter database create standby controlfile as 'E:\app\Administrator\oradata\

dboradg\control01.ctl';

Database altered.

SQL> alter database open;

Database altered.

SQL> --create init file for standby db

SQL> create pfile='c:\init_dboradg' from spfile;

File created.

SQL> --edit below parameters for standby db.

*.audit_file_dest='E:\app\Administrator\admin\DBORAdg\adump'
*.control_files='E:\app\Administrator\oradata\DBORAdg\control01.ctl','E:\app\Administrator\oradata\DBORAdg\control02.ctl','E:\app\Administrator\oradata\DBORAdg\control03.ctl'
*.db_file_name_convert='E:\app\Administrator\oradata\dbora','E:\app\Administrator\oradata\DBORAdg'
*.db_unique_name='DBORAdg'
*.db_name='DBORA'
*.diagnostic_dest='E:\app\Administrator'
*.fal_client='DBORAdg'
*.fal_server='DBORA'
*.log_archive_config='DG_CONFIG=(dbora,dboradg)'
*.log_archive_dest_1='LOCATION=e:\arch\dboradg
valid_for=(all_logfiles,all_roles)
db_unique_name=dboradg'
*.log_archive_dest_2='SERVICE=dbora ASYNC
valid_for=(online_logfiles,primary_role)
db_unique_name=dbora'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='E:\app\Administrator\oradata\dbora','E:\app\Administrator\oradata\DBORAdg'
*.standby_file_management='AUTO'
*.compatible='11.1.0.0.0'
*.local_listener='LISTENER_DBORADG'

SQL> --set environment for standby

C:\>rem create service  via ORADIM utility

C:\>oradim -NEW -SID dboradg -STARTMODE manual

Instance created.

The Oracle dboradg VSS Writer Service service is starting.

The Oracle dboradg VSS Writer Service service was started successfully.

C:\>rem configure listener (stop + start)

C:\>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 06-AUG-2011 15:16

:16

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))

The command completed successfully

C:\>lsnrctl start

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 06-AUG-2011 15:16

:21

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production

System parameter file is E:\app\Administrator\product\11.1.0\db_1\network\admin\

listener.ora

Log messages written to e:\app\administrator\diag\tnslsnr\fakorion\listener\aler

t\log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1523

ipc)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fakorion.fakhruddin.loca

l)(PORT=1523)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Produ

ction

Start Date                06-AUG-2011 15:16:23

Uptime                    0 days 0 hr. 0 min. 3 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   E:\app\Administrator\product\11.1.0\db_1\network\admin

\listener.ora

Listener Log File         e:\app\administrator\diag\tnslsnr\fakorion\listener\al

ert\log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1523ipc)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fakorion.fakhruddin.local)(PORT=1523

)))

The listener supports no services

The command completed successfully

C:\>rem configure TNSNAMES.ORA file $ORACLE_HOME/network/admin

C:\>set oracle_sid=dboradg

C:\>sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Aug 6 15:17:38 2011

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

Connected to an idle instance.

SQL> --create spfile from pfile (standby)

SQL> create spfile from pfile='c:\init_dboradg' ;

File created.

SQL> --start physical standby db

SQL> startup mount

ORACLE instance started.

Total System Global Area  150667264 bytes

Fixed Size                  1331740 bytes

Variable Size              92278244 bytes

Database Buffers           50331648 bytes

Redo Buffers                6725632 bytes

Database mounted.

SQL> --start redo log

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SQL> --verify the physical standby performing properly

SQL> --identify the existing archivelog files on standby db

SQL> select sequence#,first_time,next_time

  2  from v$archived_log order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME

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

         3 06-AUG-11 06-AUG-11

         4 06-AUG-11 06-AUG-11

         5 06-AUG-11 06-AUG-11

SQL> --force log switch on primary db

SQL> conn sys/oracle@dbora as sysdba

Connected.

SQL> alter system switch logfile;

System altered.

SQL> --verify new redo data was archived on standby db

SQL> conn sys/oracle@dboradg as sysdba

Connected.

SQL> select sequence#,first_time,next_time

  2  from v$archived_log order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME

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

         3 06-AUG-11 06-AUG-11

         4 06-AUG-11 06-AUG-11

         5 06-AUG-11 06-AUG-11

         6 06-AUG-11 06-AUG-11

SQL> --verify that received redo has been applied

SQL> select sequence#,applied from v$archived_log

  2  order by sequence#;

 SEQUENCE# APP

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

         3 YES

         4 YES

         5 YES

         6 YES

SQL> --adding new tablespace  ( standby_file_management is set to AUTO)

SQL> create tablespace TEST

  2  datafile 'E:\app\Administrator\oradata\DBORA\test01.dbf' size 1m;

Tablespace created.

SQL> --switch log to apply redo data on standby

SQL> alter system switch logfile;

System altered.

SQL> --verify new datafiles added in standby

SQL> select name from v$datafile;

NAME

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

[output cut]

E:\APP\ADMINISTRATOR\ORADATA\DBORADG\TEST01.DBF

from alertlog file

Sat Aug 06 16:10:24 2011

Media Recovery Log E:\ARCH\DBORADG\ARC00007_0758464037.001

Recovery created file E:\APP\ADMINISTRATOR\ORADATA\DBORADG\TEST01.DBF

Successfully added datafile 5 to media recovery

Datafile #5: 'E:\APP\ADMINISTRATOR\ORADATA\DBORADG\TEST01.DBF'

SQL> --drop tablespace on primary db

SQL> drop tablespace TEST including contents and datafiles;

Tablespace dropped.

SQL> --switch logfile

SQL> alter system switch logfile;

System altered.

SQL> --verify on standby database the tablespace got deleted.

SQL> select name from v$tablespace;

NAME

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

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEMP

SQL> select name from v$datafile;

NAME

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

E:\APP\ADMINISTRATOR\ORADATA\DBORA\SYSTEM01.DBF

E:\APP\ADMINISTRATOR\ORADATA\DBORA\SYSAUX01.DBF

E:\APP\ADMINISTRATOR\ORADATA\DBORA\UNDOTBS01.DBF

E:\APP\ADMINISTRATOR\ORADATA\DBORA\USERS01.DBF

from alert log file

Sat Aug 06 16:14:52 2011

Media Recovery Log E:\ARCH\DBORADG\ARC00009_0758464037.001

Recovery deleting file #5:'E:\APP\ADMINISTRATOR\ORADATA\DBORADG\TEST01.DBF' from controlfile.

WARNING: Cannot delete file E:\APP\ADMINISTRATOR\ORADATA\DBORADG\TEST01.DBF

Recovery dropped tablespace 'TEST'

Media Recovery Waiting for thread 1 sequence 10 (in transit)

On Standby Database , to verify Last Received and Last Sent Redo log.

SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT
  FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED='YES'

on standby database delete test01.dbf datafile manually.