Saturday 28 November 2015

How to configure a physical standby in 11G

My primary database name will be PROD

Standby database name will be SBY1

Below are the contents of primary Pfile :-

[oracle@node4 u01]$ cat /u01/initprod.ora
prod.__db_cache_size=322961408
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=314572800
prod.__sga_target=524288000
prod.__shared_io_pool_size=0
prod.__shared_pool_size=176160768
prod.__streams_pool_size=8388608
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prod/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_unique_name='prod'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.log_archive_config='dg_config=(prod,sby1)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_2='service=sby1 valid_for=(primary_role,online_logfiles) db_unique_name=sby1'
*.log_archive_dest_state_2='ENABLE'
prod.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
prod.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=314572800
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='prod'
*.sga_target=524288000
*.standby_file_management='auto'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'


Below are the contents of Standby Pfile

[oracle@node4 u01]$ cat initsby1.ora
*.audit_file_dest='/u01/app/oracle/admin/sby1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/sby1/control01.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sby1XDB)'
*.open_cursors=300
*.pga_aggregate_target=314572800
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=524288000
*.undo_tablespace='UNDOTBS1'

log_archive_Config='dg_config=(prod,sby1)'
log_archive_Dest_2='service=prod valid_for=(primary_role,online_logfiles) db_unique_name=prod'

db_unique_name=sby1
service_names=sby1
standby_File_management=auto
db_file_name_convert='prod','sby1'
log_file_name_convert='prod','sby1'
_____________________________________

Make sure you create appropriate directories which are mentioned below :-

/u01/app/oracle/admin/sby1/adump
/u01/app/oracle/oradata/sby1

Start the standby instance in no mount

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

File created.

SQL> startup force nomount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2214936 bytes
Variable Size             159384552 bytes
Database Buffers          356515840 bytes
Redo Buffers                3821568 bytes

Below are network files entries for TNS and Listener :-

[oracle@node4 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )

CAT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cat)
    )
  )

SBY1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sby1)
    )
  )

[oracle@node4 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sby1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = sby1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = prod)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = sby1_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = sby1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = prod_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = prod)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle


Do the following on primary database RMAN Prompt :-

[oracle@node4 ~]$ rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Nov 28 22:14:23 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=308370143)

RMAN> connect auxiliary sys@sby1

auxiliary database Password:
connected to auxiliary database: PROD (not mounted)

RMAN>  duplicate target database for standby  from active database;

Starting Duplicate Db at 28-NOV-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprod' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwsby1'   ;
}
executing Memory Script

Starting backup at 28-NOV-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
Finished backup at 28-NOV-15

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/sby1/control01.ctl';
}
executing Memory Script

Starting backup at 28-NOV-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_prod.f tag=TAG20151128T221444 RECID=4 STAMP=896998484
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-NOV-15

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/sby1/temo02.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/sby1/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/sby1/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/sby1/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/sby1/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/sby1/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/sby1/tbs1a.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/sby1/undo2.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/app/oracle/oradata/sby1/system01.dbf"   datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/sby1/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/sby1/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/sby1/users01.dbf"   datafile
 5 auxiliary format
 "/u01/app/oracle/oradata/sby1/example01.dbf"   datafile
 6 auxiliary format
 "/u01/app/oracle/oradata/sby1/tbs1a.dbf"   datafile
 7 auxiliary format
 "/u01/app/oracle/oradata/sby1/undo2.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/sby1/temo02.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 28-NOV-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf
output file name=/u01/app/oracle/oradata/sby1/system01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf
output file name=/u01/app/oracle/oradata/sby1/sysaux01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf
output file name=/u01/app/oracle/oradata/sby1/undotbs01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/prod/example01.dbf
output file name=/u01/app/oracle/oradata/sby1/example01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/prod/tbs1a.dbf
output file name=/u01/app/oracle/oradata/sby1/tbs1a.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/prod/undo2.dbf
output file name=/u01/app/oracle/oradata/sby1/undo2.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prod/users01.dbf
output file name=/u01/app/oracle/oradata/sby1/users01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-NOV-15

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=9 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/tbs1a.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=10 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/undo2.dbf
Finished Duplicate Db at 28-NOV-15

Add standby logfiles with below command :-

 alter database add standby logfile group &a ('/u01/app/oracle/oradata/sby1/&a') size 50m;

Make sure you add one extra log group on standby for standby logfiles

#Finally start the MRP Process

SQL> recover managed standby database disconnect;
Media recovery complete.

SQL> select archived,sequence#,applied,to_Char(completion_Time,'dd-mon-yyyy hh24:mi:ss') from v$archived_log

ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         57 YES       28-nov-2015 22:17:27
YES         58 YES       28-nov-2015 22:17:28
















Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)